package com.apes.fn.scm.report.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.fn.scm.report.util.SalesReportUtils;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;

import java.util.Arrays;

/**
 * @program: apes-server
 * @description:
 * @author: czs
 * @create: 2023-02-28 13:56
 **/
@Component
public class SalesReportDao extends ApesDao {


    public JSONArray findDeptReport(JSONObject param) {
        StringBuffer sf = new StringBuffer();

        deptReportSQL(param, sf);

        return toJSONArray(find(sf.toString(), param));

    }
    public JSONArray findDeptReportByWeek(JSONObject param) {
        StringBuffer sf = new StringBuffer();
        sf.append("\n" +
                "SELECT YEAR_WEEK DATE_OF_STATISTICS,\n" +
                "       COMPANY_ID,\n" +
                "       DEPT_ID,\n" +
                "       SALE_MONEY,\n" +
                "       COST,\n" +
                "       DEPT_GDP,\n" +
                "       VIP_AMOUNT,\n" +
                "       PROFIT,\n" +
                "       round((case SALE_MONEY when 0 then 1 else PROFIT/SALE_MONEY end),4)PROFIT_R_MIN ,\n" +
                "       SALE_MONEY_EX_ADVANCE,\n" +
                "       PROFIT_EX_ADVANCE,\n" +
                "       BUSINESS_CIRCLE_ID,\n" +
                "       BUSINESS_CIRCLE_NAME,\n" +
                "       TIMES,\n" +
                "       COMPANY_NAME,\n" +
                "       DEPT_NAME\n" +
                "FROM V_SALES_REPORT_WEEK A\n" +
                " where 1 = 1 \n" ) ;

        SalesReportUtils.appendCondition(sf,param,"A.",true,"DEPT_ID","YEAR_WEEK");
        sf.append(" order by YEAR_WEEK asc");

        return toJSONArray(find(sf.toString(), param));

    }

    public void deptReportSQL(JSONObject param, StringBuffer sf) {
        sf.append("\n" +
                "SELECT A.DATE_OF_STATISTICS,\n" +
                "       A.COMPANY_ID,\n" +
                "       A.DEPT_ID,\n" +
                "       A.SALE_MONEY,\n" +
                "       A.SALE_MONEY - (A.SALE_MONEY - A.DEPT_GDP) SALE_MONEY_EX_ADVANCE,\n" +
                "       A.COST,\n" +
                "       A.DEPT_GDP,\n" +
                "       A.VIP_AMOUNT,\n" +
                "       A.PROFIT,\n" +
                "       A.PROFIT - (A.SALE_MONEY - A.DEPT_GDP) PROFIT_EX_ADVANCE,\n" +
                "       round((case A.SALE_MONEY when 0 then 1 else A.PROFIT/A.SALE_MONEY end)*100,2) PROFIT_R,\n" +
                "       round((case A.SALE_MONEY when 0 then 1 else A.PROFIT/A.SALE_MONEY end),4) PROFIT_R_MIN,\n" +
                "       A.DEPT_AREA,\n" +
                "       A.BUSINESS_CIRCLE_ID,\n" +
                "       A.BUSINESS_CIRCLE_NAME,\n" +
                "       A.TYPE_DEPT,\n" +
                "       A.TIMES,\n" +
                "       A.COMPANY_NAME,\n" +
                "       A.DEPT_NAME,\n" +
                "       M.SALE_MONEY MONTH_SALE_MONEY,\n" +
                "       M.COST MONTH_COST,\n" +
                "       M.DEPT_GDP MONTH_DEPT_GDP,\n" +
                "       M.PROFIT MONTH_PROFIT,\n" +
                "       M.TIMES MONTH_TIMES\n" +
                " FROM RCL_SALES_REPORT_DAY A left join V_SALES_REPORT_MONTH M on m.MONTH_OF_STATISTICS = trunc(a.DATE_OF_STATISTICS,'MM') and A.DEPT_ID = m.DEPT_ID\n" +
                " where DATE_OF_STATISTICS >= TO_DATE(:DATE_START,'YYYY-MM-DD')\n" +
                "  and DATE_OF_STATISTICS <  TO_DATE(:DATE_END,'YYYY-MM-DD') +1 ") ;

        SalesReportUtils.appendCondition(sf,param,"A.","DEPT_ID","COMPANY_ID","TYPE_DEPT");
        sf.append(" order by a.DATE_OF_STATISTICS ASC");
    }

    public JSONArray findDeptTask(JSONObject param) {
        StringBuffer sf = new StringBuffer();

        sf.append("\n" +
                "select ID,\n" +
                "       CREATE_DATE,\n" +
                "       LAST_UPDATED_STAMP,\n" +
                "       UPDATE_DATE,\n" +
                "       CREATOR_ID,\n" +
                "       INNER_BUSINESS_TYPE,\n" +
                "       UPDATER_ID,\n" +
                "       VERSION,\n" +
                "       BUSINESS_AMT,\n" +
                "       BUSINESS_TASKS_AMT,\n" +
                "       GROSS_PROFIT_TASKS_AMT,\n" +
                "       GROSS_PROFIT_AMT,\n" +
                "       SUMMARY,\n" +
                "       YEAR_MONTH,\n" +
                "       SCENE_ID,\n" +
                "       DEPT_ID\n" +
                "from gl_store_tasks A\n" +
                " where 1 = 1 \n" ) ;

        SalesReportUtils.appendCondition(sf,param,"A.",true,"DEPT_ID","YEAR_MONTH");

        return toJSONArray(find(sf.toString(), param));

    }

    public JSONArray findDateRankDay(JSONObject param) {
        StringBuffer sf = new StringBuffer();
        String dayAdvance = "";
        if (!param.getBoolean("ADVANCE")) {
            dayAdvance ="-(sum(A.SALE_MONEY-A.DEPT_GDP))";
        }
        sf.append("select A.*,\n" +
                "       (CASE\n" +
                "            WHEN (a.TYPE_RANK_TOTAL / 3) <= SALES_TYPE_RANK THEN '优秀'\n" +
                "            WHEN ((a.TYPE_RANK_TOTAL\n" +
                "                / 3) * 2) <= SALES_TYPE_RANK THEN '一般'\n" +
                "            ELSE '靠后' END) SALES_TYPE_RANK_COMMENT\n" +
                "from (select DEPT_ID,\n" +
                "             sum(SALE_MONEY)                                                             SALE_MONEY,\n" +
                "             sum(cost)                                                                   cost,\n" +
                "             sum(DEPT_GDP)                                                               DEPT_GDP,\n" +
                "             sum(PROFIT)                                                                 PROFIT,\n" +
                "             sum(TIMES)                                                                  TIMES,\n" +
                "             sum(VIP_AMOUNT)                                                                  VIP_AMOUNT,\n" +
                "             COMPANY_ID,\n" +
                "             DATE_OF_STATISTICS,\n" +
                "             DEPT_AREA,\n" +
                "             BUSINESS_CIRCLE_ID,\n" +
                "             BUSINESS_CIRCLE_NAME,\n" +
                "             TYPE_DEPT,\n" +
                "             rank() over (partition by day,TYPE_DEPT order by sum(SALE_MONEY)\n" +
                "                 desc )                                                                  SALES_TYPE_RANK,\n" +
                "             count(distinct DEPT_ID) over (partition by day,TYPE_DEPT\n" +
                "                 )                                                                       TYPE_RANK_TOTAL,\n" +
                "             round(avg(sum(SALE_MONEY)) over ( partition by day,TYPE_DEPT), 2)\n" +
                "                                                                                         SALES_TYPE_AVG,\n" +
                "             (case when day = today then '今天' else to_char(day,'YYYY-MM-DD') end) CHAR_DATE\n" +
                "      from (select A.DEPT_ID,\n" +
                "                   sum(A.SALE_MONEY)                              SALE_MONEY,\n" +
                "                   sum(A.COST)                                    cost,\n" +
                "                   sum(A.DEPT_GDP)                                DEPT_GDP,\n" +
                "                   sum(A.PROFIT)                                  PROFIT,\n" +
                "                   sum(A.TIMES)                                   TIMES,\n" +
                "                   sum(A.VIP_AMOUNT)                                   VIP_AMOUNT,\n" +
                "                   A.COMPANY_ID,\n" +
                "                   A.DEPT_AREA,\n" +
                "                   A.BUSINESS_CIRCLE_ID,\n" +
                "                   A.BUSINESS_CIRCLE_NAME,\n" +
                "                   A.TYPE_DEPT,\n" +
                "                   a.DATE_OF_STATISTICS,\n" +
                "                   trunc(a.DATE_OF_STATISTICS)day,\n" +
                "                   trunc(sysdate)today\n" +
                "            from RCL_SALES_REPORT_DAY a\n" +
                "            group by A.DEPT_ID, A.COMPANY_ID, A.DEPT_AREA, A.BUSINESS_CIRCLE_ID, A.BUSINESS_CIRCLE_NAME, A.TYPE_DEPT,\n" +
                "                     a.DATE_OF_STATISTICS)\n" +
                "      group by DEPT_ID, COMPANY_ID, DEPT_AREA, BUSINESS_CIRCLE_ID, day,today, BUSINESS_CIRCLE_NAME,\n" +
                "               TYPE_DEPT,DATE_OF_STATISTICS) A" +
                "   where DATE_OF_STATISTICS >= TO_DATE(:DATE_START,'YYYY-MM-DD') " +
                "   and DATE_OF_STATISTICS <  TO_DATE(:DATE_END,'YYYY-MM-DD') +1\n" ) ;

        SalesReportUtils.appendCondition(sf,param,"A.",true,"DEPT_ID");

        return toJSONArray(find(sf.toString(), param));

    }
    public JSONArray findDateRankWeek(JSONObject param) {
        StringBuffer sf = new StringBuffer();
        String dayAdvance = "";
        if (!param.getBoolean("ADVANCE")) {
            dayAdvance ="-(sum(A.SALE_MONEY-A.DEPT_GDP))";
        }
        sf.append("select A.*," +
                "       (CASE\n" +
                "            WHEN (a.TYPE_RANK_TOTAL / 3) <= SALES_TYPE_RANK THEN '优秀'\n" +
                "            WHEN ((a.TYPE_RANK_TOTAL\n" +
                "                / 3) * 2) <= SALES_TYPE_RANK THEN '一般'\n" +
                "            ELSE '靠后' END) SALES_TYPE_RANK_COMMENT\n" +
                "from (select DEPT_ID,\n" +
                "             sum(SALE_MONEY)  "+dayAdvance+"                                                           SALE_MONEY,\n" +
                "             sum(cost)                                                                   cost,\n" +
                "             sum(DEPT_GDP)                                                               DEPT_GDP,\n" +
                "             sum(PROFIT)                                                                 PROFIT,\n" +
                "             sum(TIMES)                                                                  TIMES,\n" +
                "             sum(VIP_AMOUNT)                                                                  VIP_AMOUNT,\n" +
                "             COMPANY_ID,\n" +
                "             DEPT_AREA,\n" +
                "             BUSINESS_CIRCLE_ID,\n" +
                "             BUSINESS_CIRCLE_NAME,\n" +
                "             TYPE_DEPT,\n" +
                "             year_week,\n" +
                "             now_week,\n" +
                "             rank() over (partition by year_week,TYPE_DEPT order by sum(SALE_MONEY)\n" +
                "                 desc )                                                                  SALES_TYPE_RANK,\n" +
                "             count(distinct DEPT_ID) over (partition by year_week,TYPE_DEPT\n" +
                "                 )                                                                       TYPE_RANK_TOTAL,\n" +
                "             round(avg(sum(SALE_MONEY)) over ( partition by year_week,TYPE_DEPT), 2)\n" +
                "                                                                                         SALES_TYPE_AVG,\n" +
                "             (case when now_week = year_week then '本周' when now_YEAR = year then '第' || week || '周' else  year|| '年' ||'第' || week || '周' end) CHAR_DATE\n" +
                "      from (select A.DEPT_ID,\n" +
                "                   sum(A.SALE_MONEY)                              SALE_MONEY,\n" +
                "                   sum(A.COST)                                    cost,\n" +
                "                   sum(A.DEPT_GDP)                                DEPT_GDP,\n" +
                "                   sum(A.PROFIT)                                  PROFIT,\n" +
                "                   sum(A.TIMES)                                   TIMES,\n" +
                "                   sum(A.VIP_AMOUNT)                                   VIP_AMOUNT,\n" +
                "                   A.COMPANY_ID,\n" +
                "                   A.DEPT_AREA,\n" +
                "                   A.BUSINESS_CIRCLE_ID,\n" +
                "                   A.BUSINESS_CIRCLE_NAME,\n" +
                "                   A.TYPE_DEPT,\n" +
                "                   substr(YEAR_WEEK,6,2)                       week,\n" +
                "                   substr(YEAR_WEEK,0,4)                       year,\n" +
                "                   YEAR_WEEK                       year_week,\n" +
                "                   TO_CHAR(sysdate, 'yyyy-iw')              now_week,\n" +
                "                   to_number(TO_CHAR(sysdate, 'MM'))\n" +
                "                                                                  now_MONTH,\n" +
                "                   to_number(TO_CHAR(sysdate, 'YYYY'))            now_YEAR\n" +
                "            from V_SALES_REPORT_WEEK a\n" +
                "            group by A.DEPT_ID, A.COMPANY_ID, A.DEPT_AREA, A.BUSINESS_CIRCLE_ID, A.BUSINESS_CIRCLE_NAME, A.TYPE_DEPT,\n" +
                "                     a.YEAR_WEEK)\n" +
                "      group by DEPT_ID, COMPANY_ID, DEPT_AREA, BUSINESS_CIRCLE_ID, BUSINESS_CIRCLE_NAME,\n" +
                "               TYPE_DEPT, year_week,now_YEAR, now_week,week,year) A\n" +
                " where 1= 1 \n" ) ;

        SalesReportUtils.appendCondition(sf,param,"A.",true,"YEAR_WEEK","DEPT_ID");

        return toJSONArray(find(sf.toString(), param));

    }

    public JSONArray findDateRankMonth(JSONObject param) {
        StringBuffer sf = new StringBuffer();
        String dayAdvance = "";
        String monthAdvance = "";
        if (!param.getBoolean("ADVANCE")) {
            dayAdvance ="-(A.SALE_MONEY-A.DEPT_GDP)";
            monthAdvance = "-(yM.SALE_MONEY-yM.DEPT_GDP)";
        }
        sf.append("select X.*,\n" +
                "       (CASE\n" +
                "            WHEN (X.TYPE_RANK_TOTAL / 3) <= SALES_TYPE_RANK THEN '优秀'\n" +
                "            WHEN ((X.TYPE_RANK_TOTAL / 3) * 2) <= SALES_TYPE_RANK THEN '一般'\n" +
                "            ELSE '靠后' END) SALES_TYPE_RANK_COMMENT," +
                " (case when now_MONTH = YEAR_MONTH then '本月' else YEAR_MONTH || '月' end)     CHAR_DATE \n" +
                "from (select A.*,\n" +
                "             (case LAST_YEAR_SALE_MONEY when 0 then 1 else ROUND((SALE_MONEY\n" +
                "                 - LAST_YEAR_SALE_MONEY) / LAST_YEAR_SALE_MONEY, 2) end) * 100 SALE_MONEY_MOM,\n" +
                "             round(avg(SALE_MONEY) over ( partition by MONTH_OF_STATISTICS,TYPE_DEPT), 2)\n" +
                "                                                                                        SALES_TYPE_AVG,\n" +
                "             rank() over (partition by MONTH_OF_STATISTICS,TYPE_DEPT order by SALE_MONEY\n" +
                "                 desc )                                                                 SALES_TYPE_RANK,\n" +
                "             count(distinct DEPT_ID) over (partition by MONTH_OF_STATISTICS,TYPE_DEPT\n" +
                "                 )                                                                      TYPE_RANK_TOTAL\n" +
                "      from (select A.MONTH_OF_STATISTICS,\n" +
                "                   A.dept_id,\n" +
                "                   A.sale_money "+dayAdvance+" sale_money,\n" +
                "                   A.cost,\n" +
                "                   A.dept_gdp,\n" +
                "                   A.profit,\n" +
                "                   A.times,\n" +
                "                   A.VIP_AMOUNT,\n" +
                "                   A.company_id,\n" +
                "                   A.dept_area,\n" +
                "                   A.business_circle_id,\n" +
                "                   A.business_circle_name,\n" +
                "                   A.type_dept,\n" +
                "                   to_number(TO_CHAR(sysdate, 'MM'))               now_MONTH,\n" +
                "                   to_number(TO_CHAR(a.MONTH_OF_STATISTICS, 'MM')) YEAR_MONTH,\n" +
                "                   yM.SALE_MONEY "+monthAdvance+"                                   LAST_YEAR_SALE_MONEY,\n" +
                "                   yM.COST                                         LAST_YEAR_COST,\n" +
                "                   yM.DEPT_GDP                                     LAST_YEAR_DEPT_GDP,\n" +
                "                   yM.PROFIT                                       LAST_YEAR_PROFIT,\n" +
                "                   ym.TIMES                                        LAST_YEAR_TIMES\n" +
                "            from V_SALES_REPORT_MONTH A\n" +
                "                     left join V_SALES_REPORT_MONTH yM\n" +
                "                               ON yM.MONTH_OF_STATISTICS = ADD_MONTHS(TRUNC(A.MONTH_OF_STATISTICS, 'MM'), -1) AND\n" +
                "                                  A.DEPT_ID = yM.DEPT_ID) A) X\n" +
                "  WHERE MONTH_OF_STATISTICS >= TO_DATE(:DATE_START,'YYYY-MM-DD')\n" +
                "    and MONTH_OF_STATISTICS <  TO_DATE(:DATE_END,'YYYY-MM-DD') +1  \n" ) ;

        SalesReportUtils.appendCondition(sf,param,"X.",false,"DEPT_ID");

        return toJSONArray(find(sf.toString(), param));

    }

    public JSONArray findDateRankYear(JSONObject param) {
        StringBuffer sf = new StringBuffer();
        String dayAdvance = "";
        String monthAdvance = "";
        if (!param.getBoolean("ADVANCE")) {
            dayAdvance ="-(A.SALE_MONEY-A.DEPT_GDP)";
            monthAdvance = "-(yM.SALE_MONEY-yM.DEPT_GDP)";
        }
        sf.append("select X.*,\n" +
                "       (CASE\n" +
                "            WHEN (X.TYPE_RANK_TOTAL / 3) <= SALES_TYPE_RANK THEN '优秀'\n" +
                "            WHEN ((X.TYPE_RANK_TOTAL / 3) * 2) <= SALES_TYPE_RANK THEN '一般'\n" +
                "            ELSE '靠后' END) SALES_TYPE_RANK_COMMENT,\n" +
                "       (case when now_YEAR = YEAR then '本年' else YEAR||'年' end)CHAR_DATE\n" +
                "from (select A.*,\n" +
                "             ROUND((SALE_MONEY - LAST_YEAR_SALE_MONEY) / LAST_YEAR_SALE_MONEY, 2) * 100 SALE_MONEY_MOM,\n" +
                "             round(avg(SALE_MONEY) over ( partition by YEAR_OF_STATISTICS,TYPE_DEPT), 2)\n" +
                "                                                                                        SALES_TYPE_AVG,\n" +
                "             rank() over (partition by YEAR_OF_STATISTICS,TYPE_DEPT order by SALE_MONEY\n" +
                "                 desc )                                                                 SALES_TYPE_RANK,\n" +
                "             count(distinct DEPT_ID) over (partition by YEAR_OF_STATISTICS,TYPE_DEPT\n" +
                "                 )                                                                      TYPE_RANK_TOTAL\n" +
                "      from (select A.year_of_statistics,\n" +
                "                   A.dept_id,\n" +
                "                   A.sale_money"+dayAdvance+"  sale_money,\n" +
                "                   A.cost,\n" +
                "                   A.dept_gdp,\n" +
                "                   A.profit,\n" +
                "                   A.VIP_AMOUNT,\n" +
                "                   A.times,\n" +
                "                   A.company_id,\n" +
                "                   A.dept_area,\n" +
                "                   A.business_circle_id,\n" +
                "                   A.business_circle_name,\n" +
                "                   A.last_type_dept,\n" +
                "                   A.type_dept,\n" +
                "                   to_number(TO_CHAR(sysdate, 'MM'))               now_YEAR,\n" +
                "                   to_number(TO_CHAR(a.YEAR_OF_STATISTICS, 'YYYY')) YEAR,\n" +
                "                   yM.SALE_MONEY"+monthAdvance+"                                   LAST_YEAR_SALE_MONEY,\n" +
                "                   yM.COST                                         LAST_YEAR_COST,\n" +
                "                   yM.DEPT_GDP                                     LAST_YEAR_DEPT_GDP,\n" +
                "                   yM.PROFIT                                       LAST_YEAR_PROFIT,\n" +
                "                   ym.TIMES                                        LAST_YEAR_TIMES\n" +
                "            from V_SALES_REPORT_YEAR A\n" +
                "                     left join V_SALES_REPORT_YEAR yM\n" +
                "                               ON yM.YEAR_OF_STATISTICS = ADD_MONTHS(TRUNC(A.YEAR_OF_STATISTICS, 'YYYY'), -1) AND\n" +
                "                                  A.DEPT_ID = yM.DEPT_ID) A) X\n" +
                "  WHERE YEAR_OF_STATISTICS >= TO_DATE(:DATE_START,'YYYY-MM-DD')\n" +
                "    and YEAR_OF_STATISTICS <  TO_DATE(:DATE_END,'YYYY-MM-DD') +1 " +
                "    and last_type_dept = 1 \n" ) ;

        SalesReportUtils.appendCondition(sf,param,"X.",false,"DEPT_ID");

        return toJSONArray(find(sf.toString(), param));

    }

    /**
     * 详情数据
     * @param param
     * @return
     */
    public JSONArray findDeptReportDetail(JSONObject param) {
        StringBuffer sf = new StringBuffer();

        sf.append("select trunc(DATE_OF_STATISTICS, '"+param.get("FORMAT")+"') DATE_OF_STATISTICS,\n" +
                "       COMPANY_ID,\n" +
                "       DEPT_ID,\n" +
                "       sum(SALE_MONEY)                 SALE_MONEY,\n" +
                "       sum(SALE_MONEY)-(sum(SALE_MONEY)-sum(DEPT_GDP))    SALE_MONEY_EX_ADVANCE,\n" +
                "       sum(SALE_MONEY)-sum(DEPT_GDP)                 ADVANCE,\n" +
                "       sum(COST)                       COST,\n" +
                "       sum(VIP_AMOUNT)                       VIP_AMOUNT,\n" +
                "       sum(DEPT_GDP)                   DEPT_GDP,\n" +
                "       sum(PROFIT)                     PROFIT,\n" +
                "       sum(PROFIT)-(sum(SALE_MONEY)-sum(DEPT_GDP))    PROFIT_EX_ADVANCE,\n" +
                "       round((case sum(SALE_MONEY) when 0 then 1 else (sum(PROFIT) / sum(SALE_MONEY)) end)*100,2)   PROFIT_R,\n" +
                "       round((case sum(SALE_MONEY) when 0 then 1 else (sum(PROFIT) / sum(SALE_MONEY)) end),4)   PROFIT_R_MIN,\n" +
                "       BUSINESS_CIRCLE_ID,\n" +
                "       BUSINESS_CIRCLE_NAME,\n" +
                "       sum(TIMES)                      TIMES,\n" +
                "       COMPANY_NAME,\n" +
                "       DEPT_NAME,\n" +
                "       max(MONTH_SALE_MONEY)           MONTH_SALE_MONEY,\n" +
                "       max(MONTH_COST)                 MONTH_COST,\n" +
                "       max(MONTH_DEPT_GDP)             MONTH_DEPT_GDP,\n" +
                "       max(MONTH_PROFIT)               MONTH_PROFIT,\n" +
                "       max(MONTH_TIMES)                MONTH_TIMES,\n" +
                "       max(LAST_MONTH_SALE_MONEY)      LAST_MONTH_SALE_MONEY,\n" +
                "       max(LAST_MONTH_COST)            LAST_MONTH_COST,\n" +
                "       max(LAST_MONTH_DEPT_GDP)        LAST_MONTH_DEPT_GDP,\n" +
                "       max(LAST_MONTH_PROFIT)          LAST_MONTH_PROFIT,\n" +
                "       max(LAST_MONTH_TIMES)           LAST_MONTH_TIMES,\n" +
                "       max(LAST_YEAR_SALE_MONEY)       LAST_YEAR_SALE_MONEY,\n" +
                "       max(LAST_YEAR_COST)             LAST_YEAR_COST,\n" +
                "       max(LAST_YEAR_DEPT_GDP)         LAST_YEAR_DEPT_GDP,\n" +
                "       max(LAST_YEAR_PROFIT)           LAST_YEAR_PROFIT,\n" +
                "       max(LAST_YEAR_TIMES)            LAST_YEAR_TIMES\n" +
                "from (SELECT A.DATE_OF_STATISTICS,\n" +
                "             A.COMPANY_ID,\n" +
                "             A.DEPT_ID,\n" +
                "             A.SALE_MONEY,\n" +
                "             A.COST,\n" +
                "             A.DEPT_GDP,\n" +
                "             A.PROFIT,\n" +
                "             A.VIP_AMOUNT,\n" +
                "             A.DEPT_AREA,\n" +
                "             A.BUSINESS_CIRCLE_ID,\n" +
                "             A.BUSINESS_CIRCLE_NAME,\n" +
                "             A.TIMES,\n" +
                "             A.COMPANY_NAME,\n" +
                "             A.DEPT_NAME,\n" +
                "             M.SALE_MONEY  MONTH_SALE_MONEY,\n" +
                "             M.COST        MONTH_COST,\n" +
                "             M.DEPT_GDP    MONTH_DEPT_GDP,\n" +
                "             M.PROFIT      MONTH_PROFIT,\n" +
                "             m.TIMES       MONTH_TIMES,\n" +
                "             lM.SALE_MONEY LAST_MONTH_SALE_MONEY,\n" +
                "             lM.COST       LAST_MONTH_COST,\n" +
                "             lM.DEPT_GDP   LAST_MONTH_DEPT_GDP,\n" +
                "             lM.PROFIT     LAST_MONTH_PROFIT,\n" +
                "             lm.TIMES      LAST_MONTH_TIMES,\n" +
                "             yM.SALE_MONEY LAST_YEAR_SALE_MONEY,\n" +
                "             yM.COST       LAST_YEAR_COST,\n" +
                "             yM.DEPT_GDP   LAST_YEAR_DEPT_GDP,\n" +
                "             yM.PROFIT     LAST_YEAR_PROFIT,\n" +
                "             ym.TIMES      LAST_YEAR_TIMES\n" +
                "      FROM RCL_SALES_REPORT_DAY A\n" +
                "               left join V_SALES_REPORT_MONTH M on m.MONTH_OF_STATISTICS\n" +
                "                                                       = trunc(a.DATE_OF_STATISTICS, 'mm') and A.DEPT_ID = m.DEPT_ID\n" +
                "               left join V_SALES_REPORT_MONTH lm on lm.MONTH_OF_STATISTICS\n" +
                "                                                        = add_months(trunc(a.DATE_OF_STATISTICS, 'mm'), -1) and\n" +
                "                                                    A.DEPT_ID = lm.DEPT_ID\n" +
                "               left join V_SALES_REPORT_MONTH ym on ym.MONTH_OF_STATISTICS\n" +
                "                                                        = add_months(trunc(a.DATE_OF_STATISTICS, 'mm'), -12) and\n" +
                "                                                    A.DEPT_ID = ym.DEPT_ID\n" +
                "      where DATE_OF_STATISTICS >= TO_DATE(:DATE_START,'YYYY-MM-DD')\n" +
                "        and DATE_OF_STATISTICS < TO_DATE(:DATE_END,'YYYY-MM-DD')+1\n"
                ) ;

        SalesReportUtils.appendCondition(sf,param,"A.","DEPT_ID","COMPANY_ID");

        sf.append("        )\n" +
                "group by trunc(DATE_OF_STATISTICS, '"+param.get("FORMAT")+"'), DEPT_ID, COMPANY_ID,\n" +
                "         BUSINESS_CIRCLE_ID,\n" +
                "         BUSINESS_CIRCLE_NAME,\n" +
                "         COMPANY_NAME,\n" +
                "         DEPT_NAME");

        return toJSONArray(find(sf.toString(), param));

    }
    /**
     * 详情数据(不带日期)
     * @param param
     * @return
     */
    public JSONArray findDeptReportDetailNoFormat(JSONObject param) {
        StringBuffer sf = new StringBuffer();

        sf.append("select \n" +
                "       COMPANY_ID,\n" +
                "       DEPT_ID,\n" +
                "       sum(SALE_MONEY)                 SALE_MONEY,\n" +
                "       sum(SALE_MONEY)-(sum(SALE_MONEY)-sum(DEPT_GDP))    SALE_MONEY_EX_ADVANCE,\n" +
                "       sum(SALE_MONEY)-sum(DEPT_GDP)                 ADVANCE,\n" +
                "       sum(COST)                       COST,\n" +
                "       sum(DEPT_GDP)                   DEPT_GDP,\n" +
                "       sum(PROFIT)                     PROFIT,\n" +
                "       sum(VIP_AMOUNT)                     VIP_AMOUNT,\n" +
                "       sum(PROFIT)-(sum(SALE_MONEY)-sum(DEPT_GDP))    PROFIT_EX_ADVANCE,\n" +
                "       round((case sum(SALE_MONEY) when 0 then 1 else (sum(PROFIT) / sum(SALE_MONEY)) end)*100,2)   PROFIT_R,\n" +
                "       round((case sum(SALE_MONEY) when 0 then 1 else (sum(PROFIT) / sum(SALE_MONEY)) end),4)   PROFIT_R_MIN,\n" +
                "       BUSINESS_CIRCLE_ID,\n" +
                "       BUSINESS_CIRCLE_NAME,\n" +
                "       sum(TIMES)                      TIMES,\n" +
                "       COMPANY_NAME,\n" +
                "       DEPT_NAME,\n" +
                "       max(MONTH_SALE_MONEY)           MONTH_SALE_MONEY,\n" +
                "       max(MONTH_COST)                 MONTH_COST,\n" +
                "       max(MONTH_DEPT_GDP)             MONTH_DEPT_GDP,\n" +
                "       max(MONTH_PROFIT)               MONTH_PROFIT,\n" +
                "       max(MONTH_TIMES)                MONTH_TIMES,\n" +
                "       max(LAST_MONTH_SALE_MONEY)      LAST_MONTH_SALE_MONEY,\n" +
                "       max(LAST_MONTH_COST)            LAST_MONTH_COST,\n" +
                "       max(LAST_MONTH_DEPT_GDP)        LAST_MONTH_DEPT_GDP,\n" +
                "       max(LAST_MONTH_PROFIT)          LAST_MONTH_PROFIT,\n" +
                "       max(LAST_MONTH_TIMES)           LAST_MONTH_TIMES,\n" +
                "       max(LAST_YEAR_SALE_MONEY)       LAST_YEAR_SALE_MONEY,\n" +
                "       max(LAST_YEAR_COST)             LAST_YEAR_COST,\n" +
                "       max(LAST_YEAR_DEPT_GDP)         LAST_YEAR_DEPT_GDP,\n" +
                "       max(LAST_YEAR_PROFIT)           LAST_YEAR_PROFIT,\n" +
                "       max(LAST_YEAR_TIMES)            LAST_YEAR_TIMES\n" +
                "from (SELECT A.DATE_OF_STATISTICS,\n" +
                "             A.COMPANY_ID,\n" +
                "             A.DEPT_ID,\n" +
                "             A.SALE_MONEY,\n" +
                "             A.COST,\n" +
                "             A.DEPT_GDP,\n" +
                "             A.PROFIT,\n" +
                "             A.VIP_AMOUNT,\n" +
                "             A.BUSINESS_CIRCLE_ID,\n" +
                "             A.BUSINESS_CIRCLE_NAME,\n" +
                "             A.TIMES,\n" +
                "             A.COMPANY_NAME,\n" +
                "             A.DEPT_NAME,\n" +
                "             M.SALE_MONEY  MONTH_SALE_MONEY,\n" +
                "             M.COST        MONTH_COST,\n" +
                "             M.DEPT_GDP    MONTH_DEPT_GDP,\n" +
                "             M.PROFIT      MONTH_PROFIT,\n" +
                "             m.TIMES       MONTH_TIMES,\n" +
                "             lM.SALE_MONEY LAST_MONTH_SALE_MONEY,\n" +
                "             lM.COST       LAST_MONTH_COST,\n" +
                "             lM.DEPT_GDP   LAST_MONTH_DEPT_GDP,\n" +
                "             lM.PROFIT     LAST_MONTH_PROFIT,\n" +
                "             lm.TIMES      LAST_MONTH_TIMES,\n" +
                "             yM.SALE_MONEY LAST_YEAR_SALE_MONEY,\n" +
                "             yM.COST       LAST_YEAR_COST,\n" +
                "             yM.DEPT_GDP   LAST_YEAR_DEPT_GDP,\n" +
                "             yM.PROFIT     LAST_YEAR_PROFIT,\n" +
                "             ym.TIMES      LAST_YEAR_TIMES\n" +
                "      FROM RCL_SALES_REPORT_DAY A\n" +
                "               left join V_SALES_REPORT_MONTH M on m.MONTH_OF_STATISTICS\n" +
                "                                                       = trunc(a.DATE_OF_STATISTICS, 'mm') and A.DEPT_ID = m.DEPT_ID\n" +
                "               left join V_SALES_REPORT_MONTH lm on lm.MONTH_OF_STATISTICS\n" +
                "                                                        = add_months(trunc(a.DATE_OF_STATISTICS, 'mm'), -1) and\n" +
                "                                                    A.DEPT_ID = lm.DEPT_ID\n" +
                "               left join V_SALES_REPORT_MONTH ym on ym.MONTH_OF_STATISTICS\n" +
                "                                                        = add_months(trunc(a.DATE_OF_STATISTICS, 'mm'), -12) and\n" +
                "                                                    A.DEPT_ID = ym.DEPT_ID\n" +
                "      where DATE_OF_STATISTICS >= TO_DATE(:DATE_START,'YYYY-MM-DD')\n" +
                "        and DATE_OF_STATISTICS < TO_DATE(:DATE_END,'YYYY-MM-DD')+1\n"
        ) ;

        SalesReportUtils.appendCondition(sf,param,"A.","DEPT_ID","COMPANY_ID");

        sf.append("        )\n" +
                "group by DEPT_ID, COMPANY_ID,\n" +
                "         BUSINESS_CIRCLE_ID,\n" +
                "         BUSINESS_CIRCLE_NAME,\n" +
                "         COMPANY_NAME,\n" +
                "         DEPT_NAME");

        return toJSONArray(find(sf.toString(), param));

    }
//    /**
//     * 周详情数据
//     * @param param
//     * @return
//     */
//    public JSONArray findDeptReportWeekDetail(JSONObject param) {
//        StringBuffer sf = new StringBuffer();
//
//        sf.append("select trunc(DATE_OF_STATISTICS, 'YEAR') DATE_OF_STATISTICS,\n" +
//                "       COMPANY_ID,\n" +
//                "       DEPT_ID,\n" +
//                "       sum(SALE_MONEY)                 SALE_MONEY,\n" +
//                "       sum(SALE_MONEY)-(sum(SALE_MONEY)-sum(DEPT_GDP))    SALE_MONEY_EX_ADVANCE,\n" +
//                "       sum(SALE_MONEY)-sum(DEPT_GDP)                 ADVANCE,\n" +
//                "       sum(COST)                       COST,\n" +
//                "       sum(DEPT_GDP)                   DEPT_GDP,\n" +
//                "       sum(PROFIT)                     PROFIT,\n" +
//                "       sum(PROFIT)-(sum(SALE_MONEY)-sum(DEPT_GDP))    PROFIT_EX_ADVANCE,\n" +
//                "       round((sum(PROFIT) / sum(SALE_MONEY))*100,2)   PROFIT_R,\n" +
//                "       round((sum(PROFIT) / sum(SALE_MONEY)),2)   PROFIT_R_MIN,\n" +
//                "       BUSINESS_CIRCLE_ID,\n" +
//                "       BUSINESS_CIRCLE_NAME,\n" +
//                "       sum(TIMES)                      TIMES,\n" +
//                "       COMPANY_NAME,\n" +
//                "       DEPT_NAME\n" +
//                "from (SELECT A.YEAR_WEEK DATE_OF_STATISTICS,\n" +
//                "             A.COMPANY_ID,\n" +
//                "             A.DEPT_ID,\n" +
//                "             A.SALE_MONEY,\n" +
//                "             A.COST,\n" +
//                "             A.DEPT_GDP,\n" +
//                "             A.PROFIT,\n" +
//                "             A.BUSINESS_CIRCLE_ID,\n" +
//                "             A.BUSINESS_CIRCLE_NAME,\n" +
//                "             A.TIMES,\n" +
//                "             A.COMPANY_NAME,\n" +
//                "             A.DEPT_NAME\n" +
//                "      FROM V_SALES_REPORT_WEEK A\n" +
//                "      where 1 = 1\n"
//                ) ;
//
//        SalesReportUtils.appendCondition(sf,param,"A.","DEPT_ID","COMPANY_ID");
//
//        return toJSONArray(find(sf.toString(), param));
//
//    }
    /**
     * 月份服务类型详情
     * @param param
     * @return
     */
    public JSONArray findMonthServeTypeDetail(JSONObject param) {
        StringBuffer sf = new StringBuffer();

        sf.append("select A.*,rank() over (partition by a.dept_id,A.date_of_statistics order by A.sale_money desc ) sale_rank,\n" +
                        "        (case A.sale_money when 0 then 100 else (round((A.sale_money-A.cost)/A.sale_money,2))*100 end) PROFIT_R,\n" +
                        "         (case A.total_sale_money when 0 then 100 else (round((A.sale_money/A.total_sale_money),2))*100 end)total_sale_money_r,\n" +
                        "        (CASE WHEN A.SALE_MONEY = 0 THEN -1 WHEN A1.sale_money = 0 then 1 else ROUND((A.sale_money-NVL(A1.sale_money,0))/NVL(A1.sale_money,a.sale_money),2) end)*100 sale_money_MON,\n" +
                        "        (CASE WHEN A.SALE_MONEY = 0 THEN -1 WHEN A2.sale_money = 0 then 1 else ROUND((A.sale_money-NVL(A2.sale_money,0))/NVL(A2.sale_money,a.sale_money),2) end)*100 sale_money_YOY\n" +
                        " from v_sales_serve_type_report A\n" +
                        " left join v_sales_serve_type_report A1 ON A.dept_id = A1.DEPT_ID AND A.SERVE_TYPE_ID =A1.SERVE_TYPE_ID AND ADD_MONTHS(A.date_of_statistics,-1) = A1.DATE_OF_STATISTICS\n" +
                        " left join v_sales_serve_type_report A2 ON A.dept_id = A2.DEPT_ID AND A.SERVE_TYPE_ID =A2.SERVE_TYPE_ID AND ADD_MONTHS(A.date_of_statistics,-12) = A2.DATE_OF_STATISTICS\n" +
                        "  WHERE  A.DATE_OF_STATISTICS >= TO_DATE(:DATE_START,'YYYY-MM-DD') \n" +
                        "  and    A.DATE_OF_STATISTICS < TO_DATE(:DATE_END,'YYYY-MM-DD') +1 \n"
                ) ;
//        if (!param.getBoolean("ADVANCE")){
//            sf.append(" and A.SERVE_TYPE_ID not in ('010', '012') ");
//        }
        SalesReportUtils.appendCondition(sf,param,"A.",true,"DEPT_ID");


        return toJSONArray(find(sf.toString(), param));

    }

    /**
     * 营业分析详情
     * @param param
     * @return
     */
    public JSONArray businessAnalyseReportDetail(JSONObject param,String format) {
        StringBuffer sf = new StringBuffer();
        String dayAdvance = "";
        String monthAdvance = "";
        String yearAdvance = "";
        if (!param.getBoolean("ADVANCE")) {
            dayAdvance ="-(sum(A.SALE_MONEY-A.DEPT_GDP))";
            monthAdvance = "-(sum(A1.SALE_MONEY-A1.DEPT_GDP))";
            yearAdvance = "-(sum(A2.SALE_MONEY-A2.DEPT_GDP))";
        }
        sf.append("SELECT X.*,\n" +
                        "       TO_CHAR(DATE_OF_STATISTICS, 'YYYY-MM-DD')                                            DATE_OF_STATISTICS_CHAR,\n" +
                        "       (CASE nvl(BUSINESS_AMT, 0)\n" +
                        "            WHEN 0 THEN '未设目标'\n" +
                        "            ELSE ROUND((SALE_MONEY / (BUSINESS_AMT * 10000)) * 100, 2)||'' END)                 BUSINESS_AMT_PERCENT,\n" +
                        "       (CASE nvl(GROSS_PROFIT_TASKS_AMT, 0)\n" +
                        "            WHEN 0 THEN '未设目标'\n" +
                        "            ELSE ROUND((PROFIT_AMT / (GROSS_PROFIT_TASKS_AMT * 10000)) * 100, 2)||'' END)                 GROSS_PROFIT_AMT_PERCENT,\n" +
                        "       (CASE PROFIT_AMT WHEN 0 THEN 0 ELSE ROUND((SALE_MONEY / PROFIT_AMT) * 100, 2) END)   PROFIT_R_R,\n" +
                        "       (CASE SALE_MONEY_MOM_A WHEN 0 THEN 100 ELSE ROUND(((SALE_MONEY - SALE_MONEY_MOM_A) / SALE_MONEY_MOM_A) * 100, 2) END) SALE_MONEY_MOM_R,\n" +
                        "       (CASE SALE_MONEY_YOY_A WHEN 0 THEN 100 ELSE ROUND(((SALE_MONEY - SALE_MONEY_YOY_A) / SALE_MONEY_YOY_A) * 100, 2) END) SALE_MONEY_YOY_R,\n" +
                        "       (CASE PROFIT_MOM_R WHEN 0 THEN 100 ELSE ROUND(((PROFIT_R - PROFIT_MOM_R) / PROFIT_MOM_R) * 100, 2) END) PROFIT_R_MOM_R,\n" +
                        "       (CASE PROFIT_YOY_R WHEN 0 THEN 100 ELSE ROUND(((PROFIT_R - PROFIT_YOY_R) / PROFIT_YOY_R) * 100, 2) END) PROFIT_R_YOY_R,\n" +
                        "       (CASE COST_MOM_A WHEN 0 THEN 100 ELSE ROUND(((COST_AMT - COST_MOM_A) / COST_MOM_A) * 100, 2) END) COST_MOM_R,\n" +
                        "       (CASE COST_YOY_A WHEN 0 THEN 100 ELSE ROUND(((COST_AMT - COST_YOY_A) / COST_YOY_A) * 100, 2) END) COST_YOY_R,\n" +
                        "       (CASE DEPT_GDP_MOM_A WHEN 0 THEN 100 ELSE ROUND(((GDP_AMT - DEPT_GDP_MOM_A) / DEPT_GDP_MOM_A) * 100, 2) END) DEPT_GDP_MOM_R,\n" +
                        "       (CASE DEPT_GDP_YOY_A WHEN 0 THEN 100 ELSE ROUND(((GDP_AMT - DEPT_GDP_YOY_A) / DEPT_GDP_YOY_A) * 100, 2) END) DEPT_GDP_YOY_R,\n" +
                        "       (CASE CLIENT_MOM WHEN 0 THEN 100 ELSE ROUND(((CLIENT_UNIT - CLIENT_MOM) / CLIENT_MOM) * 100, 2) END) CLIENT_UNIT_MOM_R,\n" +
                        "       (CASE CLIENT_YOY WHEN 0 THEN 100 ELSE ROUND(((CLIENT_UNIT - CLIENT_YOY) / CLIENT_YOY) * 100, 2) END) CLIENT_UNIT_YOY_R,\n" +
                        "       (CASE  WHEN (X.TYPE_RANK_TOTAL/3)<=SALES_TYPE_RANK THEN '优秀' WHEN ((X.TYPE_RANK_TOTAL/3)*2)<=SALES_TYPE_RANK THEN '一般' ELSE '靠后' END) SALES_TYPE_RANK_COMMENT,\n" +
                        "        X.SALE_MONEY - SALES_AREA_AVG                                                          SALES_AREA_DIFF,\n" +
                        "        X.SALE_MONEY - SALES_AREA_BEST                                                        SALES_AREA_BEST_DIFF,\n" +
                        "        X.SALE_MONEY - SALE_CIRCLE_AVG                                                        SALE_CIRCLE_AVG_DIFF,\n" +
                        "        X.GDP_AMT    - GDP_AREA_AVG                                                              GDP_AREA_DIFF,\n" +
                        "        X.GDP_AMT    - GDP_AREA_BEST                                                            GDP_AREA_BEST_DIFF,\n" +
                        "        X.COST_AMT   - COST_AREA_AVG                                                            COST_AREA_DIFF,\n" +
                        "        X.COST_AMT   - COST_AREA_BEST                                                          COST_AREA_BEST_DIFF,\n" +
                        "        X.PROFIT_R   - PROFIT_R_AREA_AVG                                                        PROFIT_R_AREA_DIFF,\n" +
                        "        X.PROFIT_R   - PROFIT_R_AREA_BEST                                                      PROFIT_R_AREA_BEST_DIFF,\n" +
                        "        X.PROFIT_AMT - PROFIT_AREA_AVG                                                        PROFIT_AREA_DIFF,\n" +
                        "        X.PROFIT_AMT - PROFIT_AREA_BEST                                                      PROFIT_AREA_BEST_DIFF,\n" +
                        "        X.SALE_MONEY - SALES_TYPE_AVG                                                          SALES_TYPE_DIFF,\n" +
                        "        X.SALE_MONEY - SALES_TYPE_BEST                                                        SALES_TYPE_BEST_DIFF,\n" +
                        "        X.GDP_AMT    - GDP_TYPE_AVG                                                              GDP_TYPE_DIFF,\n" +
                        "        X.GDP_AMT    - GDP_TYPE_BEST                                                            GDP_TYPE_BEST_DIFF,\n" +
                        "        X.COST_AMT   - COST_TYPE_AVG                                                            COST_TYPE_DIFF,\n" +
                        "        X.COST_AMT   - COST_TYPE_BEST                                                          COST_TYPE_BEST_DIFF,\n" +
                        "        X.PROFIT_R   - PROFIT_R_TYPE_AVG                                                        PROFIT_R_TYPE_DIFF,\n" +
                        "        X.PROFIT_R   - PROFIT_R_TYPE_BEST                                                      PROFIT_R_TYPE_BEST_DIFF,\n" +
                        "        X.PROFIT_AMT - PROFIT_TYPE_AVG                                                        PROFIT_TYPE_DIFF,\n" +
                        "        X.PROFIT_AMT - PROFIT_TYPE_BEST                                                      PROFIT_TYPE_BEST_DIFF\n" +
                        "\n" +
                        " FROM (SELECT trunc(DATE_OF_STATISTICS,'"+format+"') DATE_OF_STATISTICS,\n" +
                        "             A.COMPANY_ID,\n" +
                        "             A.DEPT_ID,\n" +
                        "             SUM(A.SALE_MONEY)"+dayAdvance+" SALE_MONEY,\n" +
                        "             SUM(A.COST )                                                                         COST_AMT,\n" +
                        "             SUM(A.DEPT_GDP)                                                                      GDP_AMT,\n" +
                        "             SUM(A.VIP_AMOUNT)                                                                      VIP_AMOUNT,\n" +
                        "             SUM(A.PROFIT)"+dayAdvance+"                                                                        PROFIT_AMT,\n" +
                        "               (CASE SUM(A.SALE_MONEY) "+dayAdvance+" WHEN 0 THEN 0 ELSE ROUND(((SUM(A.PROFIT)   "+dayAdvance+") / (SUM(A.SALE_MONEY)"+dayAdvance+")) * 100, 2) END)   PROFIT_R,       " +
                        "               (CASE SUM(A1.SALE_MONEY)"+monthAdvance+" WHEN 0 THEN 0 ELSE ROUND(((SUM(A1.PROFIT)"+monthAdvance+") / (SUM(A1.SALE_MONEY)"+monthAdvance+")) * 100, 2) END)   PROFIT_MOM_R,       " +
                        "               (CASE SUM(A2.SALE_MONEY)"+yearAdvance+" WHEN 0 THEN 0 ELSE ROUND(((SUM(A2.PROFIT) "+yearAdvance+") / (SUM(A2.SALE_MONEY) "+yearAdvance+")) * 100, 2) END)   PROFIT_YOY_R,       " +
                        "             A.DEPT_AREA,\n" +
                        "             A.BUSINESS_CIRCLE_ID,\n" +
                        "             A.BUSINESS_CIRCLE_NAME,\n" +
                        "             A.TYPE_DEPT,\n" +
                        "             SUM(A.TIMES)TIMES,\n" +
                        "             AVG(NVL(A1.SALE_MONEY, 0))"+dayAdvance+"                                                           SALE_MONEY_MOM_A,\n" +
                        "             AVG(NVL(A2.SALE_MONEY, 0))"+dayAdvance+"                                                           SALE_MONEY_YOY_A,\n" +
                        "             AVG(NVL(A1.COST, 0))                                                           COST_MOM_A,\n" +
                        "             AVG(NVL(A2.COST, 0))                                                           COST_YOY_A,\n" +
                        "             AVG(NVL(A1.DEPT_GDP, 0))                                                           DEPT_GDP_MOM_A,\n" +
                        "             AVG(NVL(A2.DEPT_GDP, 0))                                                           DEPT_GDP_YOY_A,\n" +
                        "             ROUND(AVG(SUM(A.SALE_MONEY)"+dayAdvance+") OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.DEPT_AREA), 2) SALES_AREA_AVG,\n" +
                        "             ROUND(MAX(SUM(A.SALE_MONEY)"+dayAdvance+") OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.DEPT_AREA), 2) SALES_AREA_BEST,\n" +
                        "             ROUND(AVG(SUM(A.SALE_MONEY)"+dayAdvance+") OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.TYPE_DEPT), 2) SALES_TYPE_AVG,\n" +
                        "             ROUND(MAX(SUM(A.SALE_MONEY)"+dayAdvance+") OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.TYPE_DEPT), 2) SALES_TYPE_BEST,\n" +
                        "             ROUND(avg(SUM(A.SALE_MONEY)) OVER ( PARTITION BY trunc(DATE_OF_STATISTICS, '"+format+"'),A.BUSINESS_CIRCLE_ID),2)SALE_CIRCLE_AVG,\n" +
                        "             RANK() OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.TYPE_DEPT order by SUM(A.SALE_MONEY)"+dayAdvance+") SALES_TYPE_RANK,\n" +
                        "             COUNT(1) OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.TYPE_DEPT) TYPE_RANK_TOTAL,\n" +
                        "             COUNT(DISTINCT A.DEPT_ID) OVER ( PARTITION BY A.TYPE_DEPT)               TYPE_TOTAL,\n" +
                        "             ROUND(AVG(SUM(A.DEPT_GDP)) OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.DEPT_AREA), 2)   GDP_AREA_AVG,\n" +
                        "             ROUND(MAX(SUM(A.DEPT_GDP)) OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.DEPT_AREA), 2)   GDP_AREA_BEST,\n" +
                        "             ROUND(AVG(SUM(A.DEPT_GDP)) OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.TYPE_DEPT), 2)   GDP_TYPE_AVG,\n" +
                        "             ROUND(MAX(SUM(A.DEPT_GDP)) OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.TYPE_DEPT), 2)   GDP_TYPE_BEST,\n" +
                        "             ROUND(AVG(SUM(A.COST)) OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.DEPT_AREA), 2)       COST_AREA_AVG,\n" +
                        "             ROUND(MAX(SUM(A.COST)) OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.DEPT_AREA), 2)       COST_AREA_BEST,\n" +
                        "             ROUND(AVG(SUM(A.COST)) OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.TYPE_DEPT), 2)       COST_TYPE_AVG,\n" +
                        "             ROUND(MAX(SUM(A.COST)) OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.TYPE_DEPT), 2)       COST_TYPE_BEST,\n" +
                        "             ROUND(AVG(SUM(A.PROFIT)"+dayAdvance+") OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.DEPT_AREA), 2)     PROFIT_AREA_AVG,\n" +
                        "             ROUND(MAX(SUM(A.PROFIT)"+dayAdvance+") OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.DEPT_AREA), 2)     PROFIT_AREA_BEST,\n" +
                        "             ROUND(AVG(SUM(A.PROFIT)"+dayAdvance+") OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.TYPE_DEPT), 2)     PROFIT_TYPE_AVG,\n" +
                        "             ROUND(MAX(SUM(A.PROFIT)"+dayAdvance+") OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.TYPE_DEPT), 2)     PROFIT_TYPE_BEST,\n" +
                        "             ROUND(AVG((CASE SUM(A.SALE_MONEY)"+dayAdvance+" WHEN 0 THEN 0 ELSE ROUND(((SUM(A.PROFIT)"+dayAdvance+") / (SUM(A.SALE_MONEY)"+dayAdvance+")) * 100, 2) END)) OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.DEPT_AREA), 2)     PROFIT_R_AREA_AVG,\n" +
                        "             ROUND(MAX((CASE SUM(A.SALE_MONEY)"+dayAdvance+" WHEN 0 THEN 0 ELSE ROUND(((SUM(A.PROFIT)"+dayAdvance+") / (SUM(A.SALE_MONEY)"+dayAdvance+")) * 100, 2) END)) OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.DEPT_AREA), 2)     PROFIT_R_AREA_BEST,\n" +
                        "             ROUND(AVG((CASE SUM(A.SALE_MONEY)"+dayAdvance+" WHEN 0 THEN 0 ELSE ROUND(((SUM(A.PROFIT)"+dayAdvance+") / (SUM(A.SALE_MONEY)"+dayAdvance+")) * 100, 2) END)) OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.TYPE_DEPT), 2)     PROFIT_R_TYPE_AVG,\n" +
                        "             ROUND(MAX((CASE SUM(A.SALE_MONEY)"+dayAdvance+" WHEN 0 THEN 0 ELSE ROUND(((SUM(A.PROFIT)"+dayAdvance+") / (SUM(A.SALE_MONEY)"+dayAdvance+")) * 100, 2) END)) OVER ( PARTITION BY trunc(DATE_OF_STATISTICS,'"+format+"'),A.TYPE_DEPT), 2)     PROFIT_R_TYPE_BEST,\n" +
                        "             ROUND((SUM(A.SALE_MONEY )"+dayAdvance+") / (CASE SUM(A.TIMES) WHEN 0 THEN 1 ELSE SUM(A.TIMES) END), 2)          CLIENT_UNIT,\n" +
                        "             ROUND((SUM(A1.SALE_MONEY)"+dayAdvance+") / (CASE SUM(A1.TIMES) WHEN 0 THEN 1 ELSE SUM(A1.TIMES) END), 2)          CLIENT_MOM,\n" +
                        "             ROUND((SUM(A2.SALE_MONEY)"+dayAdvance+") / (CASE SUM(A2.TIMES) WHEN 0 THEN 1 ELSE SUM(A2.TIMES) END), 2)          CLIENT_YOY,\n" +
                        "             (SELECT D.NAME FROM MAS_DEPT D WHERE D.ID = A.DEPT_ID)                          DEPT_NAME,\n" +
                        "             NVL(ROUND((SELECT BUSINESS_TASKS_AMT\n" +
                        "                        FROM GL_STORE_TASKS M\n" +
                        "                        WHERE M.DEPT_ID = A.DEPT_ID\n" +
                        "                          AND TRUNC(YEAR_MONTH, 'MM') = TRUNC(trunc(DATE_OF_STATISTICS,'"+format+"'), 'MM')\n" +
                        "                          AND ROWNUM = 1) / 10000, 2), 0)                                    BUSINESS_AMT,\n" +
                        "             NVL(ROUND((SELECT GROSS_PROFIT_TASKS_AMT\n" +
                        "                        FROM GL_STORE_TASKS M\n" +
                        "                        WHERE M.DEPT_ID = A.DEPT_ID\n" +
                        "                          AND TRUNC(YEAR_MONTH, 'MM') = TRUNC(trunc(DATE_OF_STATISTICS,'"+format+"'), 'MM')\n" +
                        "                          AND ROWNUM = 1) / 10000, 2), 0)                                    GROSS_PROFIT_TASKS_AMT\n" +
                        "      FROM (select trunc(a.DATE_OF_STATISTICS,'"+format+"') DATE_OF_STATISTICS,DEPT_ID,sum(SALE_MONEY)SALE_MONEY,sum(COST)cost,sum(DEPT_GDP)DEPT_GDP,sum(PROFIT)PROFIT,sum(VIP_AMOUNT)VIP_AMOUNT,\n" +
                "                       sum(TIMES)TIMES,COMPANY_ID,MAX(DEPT_AREA)DEPT_AREA,BUSINESS_CIRCLE_ID,BUSINESS_CIRCLE_NAME,\n" +
                "                       MAX(TYPE_DEPT)TYPE_DEPT\n" +
                "                from RCL_SALES_REPORT_DAY a\n" +
                "                group by trunc(a.DATE_OF_STATISTICS,'"+format+"'),DEPT_ID,COMPANY_ID,BUSINESS_CIRCLE_ID,\n" +
                "                         BUSINESS_CIRCLE_NAME) A\n" +
                        "               LEFT JOIN V_SALES_REPORT_MONTH A1\n" +
                        "                         ON A1.MONTH_OF_STATISTICS = ADD_MONTHS(TRUNC(A.DATE_OF_STATISTICS, 'MM'), -1) AND\n" +
                        "                            A.DEPT_ID = A1.DEPT_ID\n" +
                        "               LEFT JOIN V_SALES_REPORT_MONTH A2\n" +
                        "                         ON A2.MONTH_OF_STATISTICS = ADD_MONTHS(TRUNC(A.DATE_OF_STATISTICS, 'MM'), -12) AND\n" +
                        "                            A.DEPT_ID = A2.DEPT_ID\n" +
                        "      WHERE  DATE_OF_STATISTICS >= TO_DATE(:DATE_START,'YYYY-MM-DD') " +
                "               and DATE_OF_STATISTICS <  TO_DATE(:DATE_END,'YYYY-MM-DD') +1" +
                "\n" );
        sf.append(      "    group by trunc(DATE_OF_STATISTICS,'"+format+"'),a.COMPANY_ID,A.DEPT_ID,A.DEPT_AREA,\n" +
                        "             A.BUSINESS_CIRCLE_ID,\n" +
                        "             A.BUSINESS_CIRCLE_NAME,\n" +
                        "             A.TYPE_DEPT\n" +
                        "     ) X where 1 = 1 \n" +
                        "\n"
                ) ;
        SalesReportUtils.appendCondition(sf,param,"X.","DEPT_ID","COMPANY_ID","TYPE_DEPT");


        return toJSONArray(find(sf.toString(), param));

    }


    /**
     * 营业分析详情
     * @param param
     * @return
     */
    public JSONArray businessAnalyseReportDetailNoType(JSONObject param,String format) {
        StringBuffer sf = new StringBuffer();
        String dayAdvance = "";
        String monthAdvance = "";
        String yearAdvance = "";
        if (!param.getBoolean("ADVANCE")) {
            dayAdvance ="-(sum(A.SALE_MONEY-A.DEPT_GDP))";
            monthAdvance = "-(sum(A1.SALE_MONEY-A1.DEPT_GDP))";
            yearAdvance = "-(sum(A2.SALE_MONEY-A2.DEPT_GDP))";
        }
        sf.append("SELECT X.*,\n" +
                "       TO_CHAR(DATE_OF_STATISTICS, 'YYYY-MM-DD')                                            DATE_OF_STATISTICS_CHAR,\n" +
                "       (CASE nvl(BUSINESS_AMT, 0)\n" +
                "            WHEN 0 THEN '未设目标'\n" +
                "            ELSE ROUND((SALE_MONEY / (BUSINESS_AMT * 10000)) * 100, 2)||'' END)                 BUSINESS_AMT_PERCENT,\n" +
                "       (CASE nvl(GROSS_PROFIT_TASKS_AMT, 0)\n" +
                "            WHEN 0 THEN '未设目标'\n" +
                "            ELSE ROUND((PROFIT_AMT / (GROSS_PROFIT_TASKS_AMT * 10000)) * 100, 2)||'' END)                 GROSS_PROFIT_AMT_PERCENT,\n" +
                "       (CASE PROFIT_AMT WHEN 0 THEN 0 ELSE ROUND((SALE_MONEY / PROFIT_AMT) * 100, 2) END)   PROFIT_R_R\n" +
                "\n" +
                " FROM (SELECT trunc(DATE_OF_STATISTICS,'"+format+"') DATE_OF_STATISTICS,\n" +
                "             A.COMPANY_ID,\n" +
                "             A.DEPT_ID,\n" +
                "             SUM(A.SALE_MONEY)"+dayAdvance+" SALE_MONEY,\n" +
                "             SUM(A.COST )                                                                         COST_AMT,\n" +
                "             SUM(A.DEPT_GDP)                                                                      GDP_AMT,\n" +
                "             SUM(A.VIP_AMOUNT)                                                                      VIP_AMOUNT,\n" +
                "             SUM(A.PROFIT)"+dayAdvance+"                                                                        PROFIT_AMT,\n" +
                "               (CASE SUM(A.SALE_MONEY) "+dayAdvance+" WHEN 0 THEN 0 ELSE ROUND(((SUM(A.PROFIT)   "+dayAdvance+") / (SUM(A.SALE_MONEY)"+dayAdvance+")) * 100, 2) END)   PROFIT_R,       " +
                "             A.BUSINESS_CIRCLE_ID,\n" +
                "             A.BUSINESS_CIRCLE_NAME,\n" +
                "             SUM(A.TIMES)TIMES,\n" +
                "             ROUND((SUM(A.SALE_MONEY )"+dayAdvance+") / (CASE SUM(A.TIMES) WHEN 0 THEN 1 ELSE SUM(A.TIMES) END), 2)          CLIENT_UNIT,\n" +
                "             (SELECT D.NAME FROM MAS_DEPT D WHERE D.ID = A.DEPT_ID)                          DEPT_NAME,\n" +
                "             NVL(ROUND((SELECT BUSINESS_TASKS_AMT\n" +
                "                        FROM GL_STORE_TASKS M\n" +
                "                        WHERE M.DEPT_ID = A.DEPT_ID\n" +
                "                          AND TRUNC(YEAR_MONTH, 'MM') = TRUNC(trunc(DATE_OF_STATISTICS,'"+format+"'), 'MM')\n" +
                "                          AND ROWNUM = 1) / 10000, 2), 0)                                    BUSINESS_AMT,\n" +
                "             NVL(ROUND((SELECT GROSS_PROFIT_TASKS_AMT\n" +
                "                        FROM GL_STORE_TASKS M\n" +
                "                        WHERE M.DEPT_ID = A.DEPT_ID\n" +
                "                          AND TRUNC(YEAR_MONTH, 'MM') = TRUNC(trunc(DATE_OF_STATISTICS,'"+format+"'), 'MM')\n" +
                "                          AND ROWNUM = 1) / 10000, 2), 0)                                    GROSS_PROFIT_TASKS_AMT\n" +
                "      FROM RCL_SALES_REPORT_DAY A\n" +
                "      WHERE  DATE_OF_STATISTICS >= TO_DATE(:DATE_START,'YYYY-MM-DD') " +
                "               and DATE_OF_STATISTICS <  TO_DATE(:DATE_END,'YYYY-MM-DD') +1" +
                "\n" );
        sf.append(      "    group by trunc(DATE_OF_STATISTICS,'"+format+"'),a.COMPANY_ID,A.DEPT_ID,\n" +
                "             A.BUSINESS_CIRCLE_ID,\n" +
                "             A.BUSINESS_CIRCLE_NAME\n" +
                "     ) X where 1 = 1 \n" +
                "\n"
        ) ;
        SalesReportUtils.appendCondition(sf,param,"X.","DEPT_ID","COMPANY_ID");


        return toJSONArray(find(sf.toString(), param));

    }

    /**
     * 营业分析详情
     * @param param
     * @return
     */
    public JSONArray businessAnalyseReportDetailByWeek(JSONObject param,String format) {
        StringBuffer sf = new StringBuffer();
        String dayAdvance = "";
        String monthAdvance = "";
        String yearAdvance = "";
        if (!param.getBoolean("ADVANCE")) {
            dayAdvance ="-(sum(A.SALE_MONEY-A.DEPT_GDP))";
            monthAdvance = "-(sum(A1.SALE_MONEY-A1.DEPT_GDP))";
            yearAdvance = "-(sum(A2.SALE_MONEY-A2.DEPT_GDP))";
        }
        sf.append("SELECT X.*,\n" +
                "   x.year_week DATE_OF_STATISTICS," +
                        "       (CASE nvl(BUSINESS_AMT, '--')\n" +
                        "            WHEN 0 THEN '未设目标'\n" +
                        "            ELSE ROUND((SALE_MONEY / (BUSINESS_AMT * 10000)) * 100, 2)||'' END)                 BUSINESS_AMT_PERCENT,\n" +
                        "       (CASE nvl(GROSS_PROFIT_TASKS_AMT, 0)\n" +
                        "            WHEN 0 THEN '未设目标'\n" +
                        "            ELSE ROUND((PROFIT_AMT / (GROSS_PROFIT_TASKS_AMT * 10000)) * 100, 2)||'' END)                 GROSS_PROFIT_AMT_PERCENT,\n" +
                        "       (CASE PROFIT_AMT WHEN 0 THEN 0 ELSE ROUND((SALE_MONEY / PROFIT_AMT) * 100, 2) END)   PROFIT_R_R,\n" +
                        "       (CASE SALE_MONEY_MOM_A WHEN 0 THEN 100 ELSE ROUND(((SALE_MONEY - SALE_MONEY_MOM_A) / SALE_MONEY_MOM_A) * 100, 2) END) SALE_MONEY_MOM_R,\n" +
                        "       (CASE SALE_MONEY_YOY_A WHEN 0 THEN 100 ELSE ROUND(((SALE_MONEY - SALE_MONEY_YOY_A) / SALE_MONEY_YOY_A) * 100, 2) END) SALE_MONEY_YOY_R,\n" +
                        "       (CASE PROFIT_MOM_R WHEN 0 THEN 100 ELSE ROUND(((PROFIT_R - PROFIT_MOM_R) / PROFIT_MOM_R) * 100, 2) END) PROFIT_R_MOM_R,\n" +
                        "       (CASE PROFIT_YOY_R WHEN 0 THEN 100 ELSE ROUND(((PROFIT_R - PROFIT_YOY_R) / PROFIT_YOY_R) * 100, 2) END) PROFIT_R_YOY_R,\n" +
                        "       (CASE COST_MOM_A WHEN 0 THEN 100 ELSE ROUND(((COST_AMT - COST_MOM_A) / COST_MOM_A) * 100, 2) END) COST_MOM_R,\n" +
                        "       (CASE COST_YOY_A WHEN 0 THEN 100 ELSE ROUND(((COST_AMT - COST_YOY_A) / COST_YOY_A) * 100, 2) END) COST_YOY_R,\n" +
                        "       (CASE DEPT_GDP_MOM_A WHEN 0 THEN 100 ELSE ROUND(((GDP_AMT - DEPT_GDP_MOM_A) / DEPT_GDP_MOM_A) * 100, 2) END) DEPT_GDP_MOM_R,\n" +
                        "       (CASE DEPT_GDP_YOY_A WHEN 0 THEN 100 ELSE ROUND(((GDP_AMT - DEPT_GDP_YOY_A) / DEPT_GDP_YOY_A) * 100, 2) END) DEPT_GDP_YOY_R,\n" +
                        "       (CASE CLIENT_MOM WHEN 0 THEN 100 ELSE ROUND(((CLIENT_UNIT - CLIENT_MOM) / CLIENT_MOM) * 100, 2) END) CLIENT_UNIT_MOM_R,\n" +
                        "       (CASE CLIENT_YOY WHEN 0 THEN 100 ELSE ROUND(((CLIENT_UNIT - CLIENT_YOY) / CLIENT_YOY) * 100, 2) END) CLIENT_UNIT_YOY_R,\n" +
                        "       (CASE  WHEN (X.TYPE_RANK_TOTAL/3)<=SALES_TYPE_RANK THEN '优秀' WHEN ((X.TYPE_RANK_TOTAL/3)*2)<=SALES_TYPE_RANK THEN '一般' ELSE '靠后' END) SALES_TYPE_RANK_COMMENT,\n" +
                        "       X.SALE_MONEY    -   SALES_AREA_AVG                                                           SALES_AREA_DIFF,\n" +
                        "       X.SALE_MONEY    -   SALES_AREA_BEST                                                         SALES_AREA_BEST_DIFF,\n" +
                        "       X.SALE_MONEY    -   SALE_CIRCLE_AVG                                                         SALE_CIRCLE_AVG_DIFF,\n" +
                        "       X.GDP_AMT       -   GDP_AREA_AVG                                                               GDP_AREA_DIFF,\n" +
                        "       X.GDP_AMT       -   GDP_AREA_BEST                                                             GDP_AREA_BEST_DIFF,\n" +
                        "       X.COST_AMT      -   COST_AREA_AVG                                                             COST_AREA_DIFF,\n" +
                        "       X.COST_AMT      -   COST_AREA_BEST                                                           COST_AREA_BEST_DIFF,\n" +
                        "       X.PROFIT_R      -   PROFIT_R_AREA_AVG                                                         PROFIT_R_AREA_DIFF,\n" +
                        "       X.PROFIT_R      -   PROFIT_R_AREA_BEST                                                       PROFIT_R_AREA_BEST_DIFF,\n" +
                        "       X.PROFIT_AMT    -   PROFIT_AREA_AVG                                                         PROFIT_AREA_DIFF,\n" +
                        "       X.PROFIT_AMT    -   PROFIT_AREA_BEST                                                       PROFIT_AREA_BEST_DIFF,\n" +
                        "       X.SALE_MONEY    -   SALES_TYPE_AVG                                                           SALES_TYPE_DIFF,\n" +
                        "       X.SALE_MONEY    -   SALES_TYPE_BEST                                                         SALES_TYPE_BEST_DIFF,\n" +
                        "       X.GDP_AMT       -   GDP_TYPE_AVG                                                               GDP_TYPE_DIFF,\n" +
                        "       X.GDP_AMT       -   GDP_TYPE_BEST                                                             GDP_TYPE_BEST_DIFF,\n" +
                        "       X.COST_AMT      -   COST_TYPE_AVG                                                             COST_TYPE_DIFF,\n" +
                        "       X.COST_AMT      -   COST_TYPE_BEST                                                           COST_TYPE_BEST_DIFF,\n" +
                        "       X.PROFIT_R      -   PROFIT_R_TYPE_AVG                                                         PROFIT_R_TYPE_DIFF,\n" +
                        "       X.PROFIT_R      -   PROFIT_R_TYPE_BEST                                                       PROFIT_R_TYPE_BEST_DIFF,\n" +
                        "       X.PROFIT_AMT    -   PROFIT_TYPE_AVG                                                         PROFIT_TYPE_DIFF,\n" +
                        "       X.PROFIT_AMT    -   PROFIT_TYPE_BEST                                                       PROFIT_TYPE_BEST_DIFF\n" +
                        "\n" +
                        " FROM (SELECT A.year_week ,\n" +
                        "             A.COMPANY_ID,\n" +
                        "             A.DEPT_ID,\n" +
                        "             SUM(A.SALE_MONEY)"+dayAdvance+" SALE_MONEY,\n" +
                        "             SUM(A.COST )                                                                         COST_AMT,\n" +
                        "             SUM(A.DEPT_GDP)                                                                      GDP_AMT,\n" +
                        "             SUM(A.VIP_AMOUNT)                                                                      VIP_AMOUNT,\n" +
                        "             SUM(A.PROFIT)"+dayAdvance+"                                                                        PROFIT_AMT,\n" +
                        "               (CASE SUM(A.SALE_MONEY) "+dayAdvance+" WHEN 0 THEN 0 ELSE ROUND(((SUM(A.PROFIT)   "+dayAdvance+") / (SUM(A.SALE_MONEY)"+dayAdvance+")) * 100, 2) END)   PROFIT_R,       " +
                        "               (CASE SUM(A1.SALE_MONEY)"+monthAdvance+" WHEN 0 THEN 0 ELSE ROUND(((SUM(A1.PROFIT)"+monthAdvance+") / (SUM(A1.SALE_MONEY)"+monthAdvance+")) * 100, 2) END)   PROFIT_MOM_R,       " +
                        "               (CASE SUM(A2.SALE_MONEY)"+yearAdvance+" WHEN 0 THEN 0 ELSE ROUND(((SUM(A2.PROFIT) "+yearAdvance+") / (SUM(A2.SALE_MONEY) "+yearAdvance+")) * 100, 2) END)   PROFIT_YOY_R,       " +
                        "             A.DEPT_AREA,\n" +
                        "             A.BUSINESS_CIRCLE_ID,\n" +
                        "             A.BUSINESS_CIRCLE_NAME,\n" +
                        "             A.TYPE_DEPT,\n" +
                        "             SUM(A.TIMES)TIMES,\n" +
                        "             AVG(NVL(A1.SALE_MONEY, 0))"+dayAdvance+"                                                           SALE_MONEY_MOM_A,\n" +
                        "             AVG(NVL(A2.SALE_MONEY, 0))"+dayAdvance+"                                                           SALE_MONEY_YOY_A,\n" +
                        "             AVG(NVL(A1.COST, 0))                                                           COST_MOM_A,\n" +
                        "             AVG(NVL(A2.COST, 0))                                                           COST_YOY_A,\n" +
                        "             AVG(NVL(A1.DEPT_GDP, 0))                                                           DEPT_GDP_MOM_A,\n" +
                        "             AVG(NVL(A2.DEPT_GDP, 0))                                                           DEPT_GDP_YOY_A,\n" +
                        "             ROUND(AVG(SUM(A.SALE_MONEY)"+dayAdvance+") OVER ( PARTITION BY A.YEAR_WEEK,A.DEPT_AREA), 2) SALES_AREA_AVG,\n" +
                        "             ROUND(MAX(SUM(A.SALE_MONEY)"+dayAdvance+") OVER ( PARTITION BY A.YEAR_WEEK,A.DEPT_AREA), 2) SALES_AREA_BEST,\n" +
                        "             ROUND(AVG(SUM(A.SALE_MONEY)"+dayAdvance+") OVER ( PARTITION BY A.YEAR_WEEK,A.TYPE_DEPT), 2) SALES_TYPE_AVG,\n" +
                        "             ROUND(MAX(SUM(A.SALE_MONEY)"+dayAdvance+") OVER ( PARTITION BY A.YEAR_WEEK,A.TYPE_DEPT), 2) SALES_TYPE_BEST,\n" +
                        "             ROUND(avg(SUM(A.SALE_MONEY)) OVER ( PARTITION BY  A.YEAR_WEEK,A.BUSINESS_CIRCLE_ID),2)SALE_CIRCLE_AVG,\n" +
                        "             RANK() OVER ( PARTITION BY A.YEAR_WEEK,A.TYPE_DEPT order by SUM(A.SALE_MONEY)"+dayAdvance+") SALES_TYPE_RANK,\n" +
                        "             COUNT(1) OVER ( PARTITION BY A.YEAR_WEEK,A.TYPE_DEPT) TYPE_RANK_TOTAL,\n" +
                        "             COUNT(DISTINCT A.DEPT_ID) OVER ( PARTITION BY A.TYPE_DEPT)               TYPE_TOTAL,\n" +
                        "             ROUND(AVG(SUM(A.DEPT_GDP)) OVER ( PARTITION BY A.YEAR_WEEK,A.DEPT_AREA), 2)   GDP_AREA_AVG,\n" +
                        "             ROUND(MAX(SUM(A.DEPT_GDP)) OVER ( PARTITION BY A.YEAR_WEEK,A.DEPT_AREA), 2)   GDP_AREA_BEST,\n" +
                        "             ROUND(AVG(SUM(A.DEPT_GDP)) OVER ( PARTITION BY A.YEAR_WEEK,A.TYPE_DEPT), 2)   GDP_TYPE_AVG,\n" +
                        "             ROUND(MAX(SUM(A.DEPT_GDP)) OVER ( PARTITION BY A.YEAR_WEEK,A.TYPE_DEPT), 2)   GDP_TYPE_BEST,\n" +
                        "             ROUND(AVG(SUM(A.COST)) OVER ( PARTITION BY A.YEAR_WEEK,A.DEPT_AREA), 2)       COST_AREA_AVG,\n" +
                        "             ROUND(MAX(SUM(A.COST)) OVER ( PARTITION BY A.YEAR_WEEK,A.DEPT_AREA), 2)       COST_AREA_BEST,\n" +
                        "             ROUND(AVG(SUM(A.COST)) OVER ( PARTITION BY A.YEAR_WEEK,A.TYPE_DEPT), 2)       COST_TYPE_AVG,\n" +
                        "             ROUND(MAX(SUM(A.COST)) OVER ( PARTITION BY A.YEAR_WEEK,A.TYPE_DEPT), 2)       COST_TYPE_BEST,\n" +
                        "             ROUND(AVG(SUM(A.PROFIT)"+dayAdvance+") OVER ( PARTITION BY A.YEAR_WEEK,A.DEPT_AREA), 2)     PROFIT_AREA_AVG,\n" +
                        "             ROUND(MAX(SUM(A.PROFIT)"+dayAdvance+") OVER ( PARTITION BY A.YEAR_WEEK,A.DEPT_AREA), 2)     PROFIT_AREA_BEST,\n" +
                        "             ROUND(AVG(SUM(A.PROFIT)"+dayAdvance+") OVER ( PARTITION BY A.YEAR_WEEK,A.TYPE_DEPT), 2)     PROFIT_TYPE_AVG,\n" +
                        "             ROUND(MAX(SUM(A.PROFIT)"+dayAdvance+") OVER ( PARTITION BY A.YEAR_WEEK,A.TYPE_DEPT), 2)     PROFIT_TYPE_BEST,\n" +
                        "             ROUND(AVG((CASE SUM(A.SALE_MONEY)"+dayAdvance+" WHEN 0 THEN 0 ELSE ROUND(((SUM(A.PROFIT)"+dayAdvance+") / (SUM(A.SALE_MONEY)"+dayAdvance+")) * 100, 2) END)) OVER ( PARTITION BY A.YEAR_WEEK,A.DEPT_AREA), 2)     PROFIT_R_AREA_AVG,\n" +
                        "             ROUND(MAX((CASE SUM(A.SALE_MONEY)"+dayAdvance+" WHEN 0 THEN 0 ELSE ROUND(((SUM(A.PROFIT)"+dayAdvance+") / (SUM(A.SALE_MONEY)"+dayAdvance+")) * 100, 2) END)) OVER ( PARTITION BY A.YEAR_WEEK,A.DEPT_AREA), 2)     PROFIT_R_AREA_BEST,\n" +
                        "             ROUND(AVG((CASE SUM(A.SALE_MONEY)"+dayAdvance+" WHEN 0 THEN 0 ELSE ROUND(((SUM(A.PROFIT)"+dayAdvance+") / (SUM(A.SALE_MONEY)"+dayAdvance+")) * 100, 2) END)) OVER ( PARTITION BY A.YEAR_WEEK,A.TYPE_DEPT), 2)     PROFIT_R_TYPE_AVG,\n" +
                        "             ROUND(MAX((CASE SUM(A.SALE_MONEY)"+dayAdvance+" WHEN 0 THEN 0 ELSE ROUND(((SUM(A.PROFIT)"+dayAdvance+") / (SUM(A.SALE_MONEY)"+dayAdvance+")) * 100, 2) END)) OVER ( PARTITION BY A.YEAR_WEEK,A.TYPE_DEPT), 2)     PROFIT_R_TYPE_BEST,\n" +
                        "             ROUND((SUM(A.SALE_MONEY )"+dayAdvance+") / (CASE SUM(A.TIMES) WHEN 0 THEN 1 ELSE SUM(A.TIMES) END), 2)          CLIENT_UNIT,\n" +
                        "             ROUND((SUM(A1.SALE_MONEY)"+dayAdvance+") / (CASE SUM(A1.TIMES) WHEN 0 THEN 1 ELSE SUM(A1.TIMES) END), 2)          CLIENT_MOM,\n" +
                        "             ROUND((SUM(A2.SALE_MONEY)"+dayAdvance+") / (CASE SUM(A2.TIMES) WHEN 0 THEN 1 ELSE SUM(A2.TIMES) END), 2)          CLIENT_YOY,\n" +
                        "             (SELECT D.NAME FROM MAS_DEPT D WHERE D.ID = A.DEPT_ID)                          DEPT_NAME,\n" +
                        "             NVL(ROUND((SELECT BUSINESS_TASKS_AMT\n" +
                        "                        FROM GL_STORE_TASKS M\n" +
                        "                        WHERE M.DEPT_ID = A.DEPT_ID\n" +
                        "                          AND TRUNC(YEAR_MONTH, 'MM') = TRUNC(A.MIN_DATE, 'MM')\n" +
                        "                          AND ROWNUM = 1) / 10000, 2), 0)                                    BUSINESS_AMT,\n" +
                        "             NVL(ROUND((SELECT GROSS_PROFIT_TASKS_AMT\n" +
                        "                        FROM GL_STORE_TASKS M\n" +
                        "                        WHERE M.DEPT_ID = A.DEPT_ID\n" +
                        "                          AND TRUNC(YEAR_MONTH, 'MM') = TRUNC(A.MIN_DATE, 'MM')\n" +
                        "                          AND ROWNUM = 1) / 10000, 2), 0)                                    GROSS_PROFIT_TASKS_AMT\n" +
                        "      FROM V_SALES_REPORT_WEEK A\n" +
                        "               LEFT JOIN V_SALES_REPORT_WEEK A1\n" +
                        "                         ON A1.YEAR_WEEK = A.LAST_WEEK AND\n" +
                        "                            A.DEPT_ID = A1.DEPT_ID\n" +
                        "               LEFT JOIN V_SALES_REPORT_WEEK A2\n" +
                        "                         ON A2.YEAR_WEEK = A.LAST_YEAR_WEEK AND\n" +
                        "                            A.DEPT_ID = A2.DEPT_ID\n" +
                        "      WHERE  1 = 1" +
                "\n" );
        SalesReportUtils.appendCondition(sf,param,"A.","YEAR_WEEK");

        sf.append(      "    group by A.year_week,a.COMPANY_ID,A.DEPT_ID,A.DEPT_AREA,A.MIN_DATE,\n" +
                        "             A.BUSINESS_CIRCLE_ID,\n" +
                        "             A.BUSINESS_CIRCLE_NAME,\n" +
                        "             A.TYPE_DEPT\n" +
                        "     ) X where 1 = 1 \n" +
                        "\n"
                ) ;
        SalesReportUtils.appendCondition(sf,param,"X.","YEAR_WEEK","DEPT_ID","COMPANY_ID","TYPE_DEPT");


        return toJSONArray(find(sf.toString(), param));

    }

    public JSONArray gdpAndDeductByDay(JSONObject param){
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT %GROUP%," +
                "        ''\n" +
                "        FROM RCL_SALES_PERSON_REPORT_DAY A\n" +
                "        WHERE DATE_OF_STATISTICS >= TO_DATE(:DATE_START,'YYYY-MM-DD')"+
                "           and DATE_OF_STATISTICS <  TO_DATE(:DATE_END,'YYYY-MM-DD') +1 "+
                "        ");

        SalesReportUtils.appendCondition(sf,param,"A.",true,"DEPT_ID");
        SalesReportUtils.appendCondition(sf,param,"A.","PERSON_ID","POST_ID");

        SalesReportUtils.appendGroup(sf,param,"%GROUP%,",
                Arrays.asList(
                    Arrays.asList("SUM","A.","SALE_MONEY","COST","SALES_GDP","WORK_GDP","PERSON_GDP","SALES_DEDUCT","WORK_DEDUCT","PERSON_DEDUCT","PROFIT","TIMES")
                ),
                new String[]{"DEPT_ID","DEPT_ID","DEPT_NAME"},
                new String[]{"DEPT_ID","COMPANY_ID","COMPANY_NAME"}
                );
//        sf.append(" ORDER BY A.TJRQ");
        return toJSONArray(find(sf.toString(), param));
    }


    public JSONArray gdpAndDeductByMonth(JSONObject jo) {
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT %GROUP%," +
                "        round((CASE sum(NVL(A1.PERSON_GDP,0)) WHEN 0 THEN 100 ELSE (sum((A.PERSON_GDP-A1.PERSON_GDP))/sum(A1.PERSON_GDP))*100 END )) PERSON_GDP_MOM,\n" +
                "        round((CASE sum(NVL(A1.PROFIT,0)) WHEN 0 THEN 100 ELSE (sum((A.PROFIT-A1.PROFIT))/sum(A1.PROFIT))*100 END )) PROFIT_MOM,\n" +
                "        ROUND((CASE sum(NVL(A1.PERSON_DEDUCT,0)) WHEN 0 THEN 100 ELSE (sum((A.PERSON_DEDUCT-A1.PERSON_DEDUCT))/sum(A1.PERSON_DEDUCT))*100 END ),2) PERSON_DEDUCT_MOM,\n" +
                "        ROUND((CASE sum(NVL(a2.PERSON_GDP,0)) WHEN 0 THEN 100 ELSE (sum((A.PERSON_GDP-a2.PERSON_GDP))/sum(a2.PERSON_GDP))*100 END ),2) PERSON_GDP_YOY,\n" +
                "        ROUND((CASE sum(NVL(a2.PROFIT,0)) WHEN 0 THEN 100 ELSE (sum((A.PROFIT-a2.PROFIT))/sum(a2.PROFIT))*100 END ),2) PROFIT_YOY,\n" +
                "        ROUND((CASE sum(NVL(a2.PERSON_DEDUCT,0)) WHEN 0 THEN 100 ELSE (sum((A.PERSON_DEDUCT-a2.PERSON_DEDUCT))/sum(a2.PERSON_DEDUCT))*100 END ),2) PERSON_DEDUCT_YOY\n" +
                "        FROM V_SALES_PERSON_REPORT_MONTH A" +
                "       left join V_SALES_PERSON_REPORT_MONTH A1 on add_months(a.MONTH_OF_STATISTICS,-1) =a1.MONTH_OF_STATISTICS and A.DEPT_ID = a1.DEPT_ID and A.PERSON_ID = a1.PERSON_ID\n" +
                "       left join V_SALES_PERSON_REPORT_MONTH A2 on add_months(a.MONTH_OF_STATISTICS,-12) =a2.MONTH_OF_STATISTICS and A.DEPT_ID = a2.DEPT_ID and A.PERSON_ID = a2.PERSON_ID\n" +
                "        WHERE A.MONTH_OF_STATISTICS >= TO_DATE(:DATE_START,'YYYY-MM-DD')"+
                "           and A.MONTH_OF_STATISTICS <  TO_DATE(:DATE_END,'YYYY-MM-DD') +1 "+
                "        ");

        SalesReportUtils.appendCondition(sf,jo,"A.",true,"DEPT_ID");
        SalesReportUtils.appendCondition(sf,jo,"A.","PERSON_ID","POST_ID");

        SalesReportUtils.appendGroup(sf,jo,"%GROUP%,",
                Arrays.asList(
                        Arrays.asList("SUM","A.","SALE_MONEY","COST","SALES_GDP","WORK_GDP","PERSON_GDP","SALES_DEDUCT","WORK_DEDUCT","PERSON_DEDUCT","PROFIT","TIMES")
                ),
                new String[]{"DEPT_ID","A.DEPT_ID","A.DEPT_NAME"},
                new String[]{"DEPT_ID","A.COMPANY_ID","A.COMPANY_NAME"}
        );
//        sf.append(" ORDER BY A.TJRQ");
        return toJSONArray(find(sf.toString(), jo));
    }

    public JSONArray salesReportProfitRankByPerson(JSONObject jo) {
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT X.*,\n" +
                "        RANK() OVER ( PARTITION BY TRUNC(DATE_OF_STATISTICS,'"+jo.get("FORMAT")+"'),X.DEPT_ID ORDER BY X.PROFIT DESC,x.TIMES DESC,X.POST_ID,X.PERSON_ID  )RANK,\n" +
                "        (CASE TOTAL_PROFIT_BY_DAY WHEN 0 THEN 0 ELSE ROUND((X.PROFIT/TOTAL_PROFIT_BY_DAY)*100,2) END) PROFIT_PROPORTION,\n" +
                "        (CASE NVL(A1.PROFIT,0) WHEN 0 THEN '--' ELSE ROUND(((X.PROFIT-A1.PROFIT)/A1.PROFIT)*100,2)||'' END) PROFIT_MOM\n" +
                " FROM (SELECT TRUNC(DATE_OF_STATISTICS,'"+jo.get("FORMAT")+"')DATE_OF_STATISTICS,DEPT_ID,PERSON_ID,PERSON_NAME,POST_ID,POST_NAME,\n" +
                "        SUM(A.TIMES)TIMES,SUM(A.SALE_MONEY)SALE_MONEY,SUM(A.PROFIT)PROFIT,(CASE SUM(A.TIMES) WHEN 0 THEN SUM(A.SALE_MONEY) ELSE ROUND(SUM(A.SALE_MONEY)/SUM(A.TIMES),2) END)CLIENT_UNIT_PRICE,\n" +
                "        SUM(SUM(PROFIT))OVER ( PARTITION BY TRUNC(DATE_OF_STATISTICS,'"+jo.get("FORMAT")+"'),A.DEPT_ID) TOTAL_PROFIT_BY_DAY\n" +
                " FROM RCL_SALES_PERSON_REPORT_DAY A\n" +
                " WHERE  PROFIT <> 0 " +
                "    and A.DATE_OF_STATISTICS >= TO_DATE(:DATE_START,'YYYY-MM-DD')"+
                "    and A.DATE_OF_STATISTICS  <  TO_DATE(:DATE_END,'YYYY-MM-DD')+1 "+
                "        ");

        SalesReportUtils.appendCondition(sf,jo,"A.",true,"DEPT_ID");
        sf.append(" GROUP BY TRUNC(DATE_OF_STATISTICS,'"+jo.get("FORMAT")+"'),A.DEPT_ID,A.PERSON_ID,PERSON_NAME,POST_ID,POST_NAME)X\n" +
                " LEFT JOIN V_SALES_PERSON_REPORT_MONTH A1 ON ADD_MONTHS(TRUNC(X.DATE_OF_STATISTICS,'MM'),-1) = A1.MONTH_OF_STATISTICS AND X.DEPT_ID = A1.DEPT_ID AND X.PERSON_ID = A1.PERSON_ID");
        String s = sf.toString();
        String replace = s.replace("TRUNC(DATE_OF_STATISTICS,'yyyy'),", "")
                .replace("TRUNC(DATE_OF_STATISTICS,'yyyy')DATE_OF_STATISTICS,","sysdate DATE_OF_STATISTICS,");
        return toJSONArray(find(replace, jo));
    }

    public JSONArray salesReportProfitRankByPost(JSONObject jo) {
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT X.*,\n" +
                "        RANK() OVER ( PARTITION BY TRUNC(DATE_OF_STATISTICS,'"+jo.get("FORMAT")+"'),X.DEPT_ID ORDER BY X.PROFIT DESC,x.TIMES DESC,X.POST_ID  )RANK,\n" +
                "        (CASE TOTAL_PROFIT_BY_DAY WHEN 0 THEN 0 ELSE ROUND((X.PROFIT/TOTAL_PROFIT_BY_DAY)*100,2) END) PROFIT_PROPORTION,\n" +
                "        (CASE NVL(A1.PROFIT,0) WHEN 0 THEN '--' ELSE ROUND(((X.PROFIT-A1.PROFIT)/A1.PROFIT)*100,2)||'' END) PROFIT_MOM\n" +
                " FROM (SELECT TRUNC(DATE_OF_STATISTICS,'"+jo.get("FORMAT")+"')DATE_OF_STATISTICS,DEPT_ID,POST_ID,POST_NAME,\n" +
                "        SUM(A.TIMES)TIMES,SUM(A.PROFIT)PROFIT,(CASE SUM(A.TIMES) WHEN 0 THEN SUM(A.PROFIT) ELSE ROUND(SUM(A.PROFIT)/SUM(A.TIMES),2) END)CLIENT_UNIT_PRICE,\n" +
                "        SUM(SUM(PROFIT))OVER ( PARTITION BY TRUNC(DATE_OF_STATISTICS,'"+jo.get("FORMAT")+"'),A.DEPT_ID) TOTAL_PROFIT_BY_DAY\n" +
                " FROM v_SALES_POST_REPORT_DAY A\n" +
                " WHERE PROFIT <> 0 " +
                "    and A.DATE_OF_STATISTICS >= TO_DATE(:DATE_START,'YYYY-MM-DD')"+
                "    and A.DATE_OF_STATISTICS  <  TO_DATE(:DATE_END,'YYYY-MM-DD')+1 "+
                "        ");

        SalesReportUtils.appendCondition(sf,jo,"A.",true,"DEPT_ID");
        sf.append(" GROUP BY TRUNC(DATE_OF_STATISTICS,'"+jo.get("FORMAT")+"'),A.DEPT_ID,POST_ID,POST_NAME)X\n" +
                " LEFT JOIN v_SALES_POST_REPORT_MONTH A1 ON ADD_MONTHS(TRUNC(X.DATE_OF_STATISTICS,'MM'),-1) = A1.MONTH_OF_STATISTICS AND X.DEPT_ID = A1.DEPT_ID AND X.POST_ID = A1.POST_ID");
        String s = sf.toString();
        String replace = s.replace("TRUNC(DATE_OF_STATISTICS,'yyyy'),", "")
                .replace("TRUNC(DATE_OF_STATISTICS,'yyyy')DATE_OF_STATISTICS,","sysdate DATE_OF_STATISTICS,");
        return toJSONArray(find(replace, jo));
    }
    public JSONArray findByCategoryServeType(JSONObject jo) {
        StringBuffer sf = new StringBuffer();
        sf.append("select distinct SERVE_TYPE_NAME NAME,SERVE_TYPE_ID ID\n" +
                "from RCL_CATEGORY_REPORT_DAY"
                );
        if (jo.get("key")!=null){
            jo.put("key","%"+jo.getString("key")+"%");
            sf.append(" where (SERVE_TYPE_ID like :key or SERVE_TYPE_NAME like :key)");
        }
        String s = "select decode(NAME,'接案介绍费','定损介绍费',NAME)NAME,ID  " +
                "from ("+sf.toString()+") A " +
                "";
        return toJSONArray(find(s, jo));
    }


    public JSONArray findBPDetail(JSONObject jo){
        if (jo.get("DATE_START") instanceof String){
            jo.put("DATE_START",jo.getDate("DATE_START"));
            jo.put("DATE_END",jo.getDate("DATE_END"));
        }
        StringBuffer sf = new StringBuffer();
        sf.append("select x.*,\n" +
                "       D.POST_ID,\n" +
                "       (SELECT NAME FROM HR_JOB WHERE ID = D.JOB_ID)                                      JOB_NAME\n" +
                "from (SELECT NVL(SUM(AMOUNT), 0) AMOUNT, DEPT_ID, COMPANY_ID,\n" +
                "                    SUM(TIMES) TIMES,MAIN_SALESMAN PERSON_ID,DATE_APPROVE,\n" +
                "                    SUM(SPRAY_PAINT_NUMBER) SPRAY_PAINT_NUMBER\n" +
                "               FROM (SELECT SUM(AMOUNT) AMOUNT, DEPT_ID, COMPANY_ID,\n" +
                "                             CAR_LICENSE, TIMES,MAIN_SALESMAN,DATE_APPROVE,\n" +
                "                             SUM(SPRAY_PAINT_NUMBER) SPRAY_PAINT_NUMBER\n" +
                "                        FROM (SELECT B.PRICE_TOTAL - B.DISCOUNT_AMOUNT AMOUNT,\n" +
                "                                      C.DEPT_ID, C.COMPANY_ID, CAR_LICENSE,B.MAIN_SALESMAN,TRUNC(C.DATE_APPROVE)DATE_APPROVE,\n" +
                "                                      (CASE\n" +
                "                                        WHEN CAR_LICENSE IS NOT NULL AND\n" +
                "                                             B.PRICE_TOTAL > 0 THEN\n" +
                "                                         1\n" +
                "                                        WHEN CAR_LICENSE IS NOT NULL AND\n" +
                "                                             B.PRICE_TOTAL < 0 THEN\n" +
                "                                         -1\n" +
                "                                        ELSE\n" +
                "                                         0\n" +
                "                                      END) TIMES,\n" +
                "                                      (CASE\n" +
                "                                        WHEN P.CLASSIFY_ID LIKE 'S01%' THEN\n" +
                "                                         TO_NUMBER(NVL(P.SPECIFICATIONS, 0)) *\n" +
                "                                            b.PRODUCT_UOM_QTY\n" +
                "                                        ELSE\n" +
                "                                         0\n" +
                "                                      END) SPRAY_PAINT_NUMBER\n" +
                "                                 FROM RETAIL_ORDER_ITEM B, \n" +
                "                                      MAS_PRODUCT P, RETAIL_ORDER C\n" +
                "                                 LEFT JOIN FN_CUSTOMER_EXPAND D\n" +
                "                                   ON C.CUSTOMER_EXPAND_ID = D.ID\n" +
                "                                WHERE B.SALE_ID = C.ID\n" +
                "                                  AND C.CLAIM_TYPE IS NULL\n" +
                "                                  AND GET_SERVE_TYPE_ID(B.PRODUCT_ID) = '007'\n" +
                "                                  AND P.ID = B.PRODUCT_ID\n" +
                "                                  AND C.DATE_APPROVE >= :DATE_START\n" +
                "                                  AND C.DATE_APPROVE < :DATE_END +1)\n" +
                "                       GROUP BY DEPT_ID, COMPANY_ID, TIMES, CAR_LICENSE,MAIN_SALESMAN,DATE_APPROVE)\n" +
                "              GROUP BY DEPT_ID, COMPANY_ID,MAIN_SALESMAN,DATE_APPROVE\n" +
                "             union all\n" +
                "             SELECT NVL(SUM(AMOUNT), 0) AMOUNT, DEPT_ID, COMPANY_ID,\n" +
                "                    SUM(TIMES) TIMES,CREATOR_ID,DATE_DONE,\n" +
                "                    SUM(SPRAY_PAINT_NUMBER) SPRAY_PAINT_NUMBER\n" +
                "               FROM (SELECT SUM(AMOUNT) AMOUNT, DEPT_ID, COMPANY_ID,\n" +
                "                             CAR_LICENSE, TIMES,CREATOR_ID,DATE_DONE,\n" +
                "                             SUM(SPRAY_PAINT_NUMBER) SPRAY_PAINT_NUMBER\n" +
                "                        FROM (SELECT B.PRICE_TOTAL AMOUNT, C.DEPT_ID,\n" +
                "                                      C.COMPANY_ID, CAR_LICENSE,c.CREATOR_ID,TRUNC(C.DATE_DONE)DATE_DONE,\n" +
                "                                      (CASE\n" +
                "                                        WHEN CAR_LICENSE IS NOT NULL AND\n" +
                "                                             B.PRICE_TOTAL > 0 THEN\n" +
                "                                         1\n" +
                "                                        WHEN CAR_LICENSE IS NOT NULL AND\n" +
                "                                             B.PRICE_TOTAL < 0 THEN\n" +
                "                                         -1\n" +
                "                                        ELSE\n" +
                "                                         0\n" +
                "                                      END) TIMES,\n" +
                "                                      (CASE\n" +
                "                                        WHEN P.CLASSIFY_ID LIKE 'S01%' THEN\n" +
                "                                         (CASE\n" +
                "                                           WHEN B.PRICE_TOTAL > 0 THEN\n" +
                "                                            TO_NUMBER(NVL(P.SPECIFICATIONS, 0)) *\n" +
                "                                            b.PRODUCT_UOM_QTY\n" +
                "                                           ELSE\n" +
                "                                            -1 * TO_NUMBER(NVL(P.SPECIFICATIONS, 0))\n" +
                "                                         END)\n" +
                "                                        ELSE\n" +
                "                                         0\n" +
                "                                      END) SPRAY_PAINT_NUMBER\n" +
                "                                 FROM sale_order_ITEM B,\n" +
                "                                      MAS_PRODUCT P, sale_order C\n" +
                "                                 LEFT JOIN FN_CUSTOMER_EXPAND D\n" +
                "                                   ON C.CUSTOMER_EXPAND_ID = D.ID\n" +
                "                                WHERE B.SALE_ID = C.ID\n" +
                "                                  AND GET_SERVE_TYPE_ID(B.PRODUCT_ID) = '007'\n" +
                "                                  AND P.ID = B.PRODUCT_ID\n" +
                "                                  AND C.DATE_DONE >= :DATE_START\n" +
                "                                  AND C.DATE_DONE < :DATE_END +1)\n" +
                "                       GROUP BY DEPT_ID, COMPANY_ID, TIMES, CAR_LICENSE,CREATOR_ID,DATE_DONE)\n" +
                "              GROUP BY DEPT_ID, COMPANY_ID,CREATOR_ID,DATE_DONE\n" +
                "             UNION ALL\n" +
                "             SELECT NVL(SUM(AMOUNT), 0) AMOUNT, DEPT_ID, COMPANY_ID,\n" +
                "                    SUM(TIMES) TIMES,CREATOR_ID,DATE_DONE,\n" +
                "                    SUM(SPRAY_PAINT_NUMBER) SPRAY_PAINT_NUMBER\n" +
                "               FROM (SELECT SUM(AMOUNT) AMOUNT, DEPT_ID, COMPANY_ID,\n" +
                "                             CAR_LICENSE, TIMES,CREATOR_ID,DATE_DONE,\n" +
                "                             SUM(SPRAY_PAINT_NUMBER) SPRAY_PAINT_NUMBER\n" +
                "                        FROM (SELECT B.PRICE_TOTAL * -1 AMOUNT, C.DEPT_ID,\n" +
                "                                      C.COMPANY_ID, CAR_LICENSE, -1 TIMES,C.CREATOR_ID,TRUNC(C.DATE_DONE)DATE_DONE,\n" +
                "                                      (CASE\n" +
                "                                        WHEN P.CLASSIFY_ID LIKE 'S01%' THEN\n" +
                "                                         -1 * TO_NUMBER(NVL(P.SPECIFICATIONS, 0)) *\n" +
                "                                         b.PRODUCT_UOM_QTY\n" +
                "                                        ELSE\n" +
                "                                         0\n" +
                "                                      END) SPRAY_PAINT_NUMBER\n" +
                "                                 FROM SALE_RETURN_ITEM B,\n" +
                "                                      MAS_PRODUCT P, SALE_RETURN C\n" +
                "                                 LEFT JOIN FN_CUSTOMER_EXPAND D\n" +
                "                                   ON C.CUSTOMER_EXPAND_ID = D.ID\n" +
                "                                WHERE B.SALE_RETURN_ID = C.ID\n" +
                "                                  AND GET_SERVE_TYPE_ID(B.PRODUCT_ID) = '007'\n" +
                "                                  AND P.ID = B.PRODUCT_ID\n" +
                "                                  AND C.DATE_DONE >= :DATE_START\n" +
                "                                  AND C.DATE_DONE < :DATE_END +1)\n" +
                "                       GROUP BY DEPT_ID, COMPANY_ID, TIMES, CAR_LICENSE,CREATOR_ID,DATE_DONE)\n" +
                "              GROUP BY DEPT_ID, COMPANY_ID,CREATOR_ID,DATE_DONE\n" +
                "\n" +
                "             UNION ALL\n" +
                "             SELECT NVL(SUM(AMOUNT), 0) AMOUNT, DEPT_ID, COMPANY_ID,\n" +
                "                    SUM(TIMES) TIMES,CREATOR_ID,CREATE_DATE,\n" +
                "                    SUM(SPRAY_PAINT_NUMBER) SPRAY_PAINT_NUMBER\n" +
                "               FROM (SELECT SUM(AMOUNT) AMOUNT, DEPT_ID, COMPANY_ID,\n" +
                "                             CAR_LICENSE, TIMES,CREATOR_ID,CREATE_DATE,\n" +
                "                             SUM(SPRAY_PAINT_NUMBER) SPRAY_PAINT_NUMBER\n" +
                "                        FROM (SELECT B.PRICE_TOTAL AMOUNT, C.DEPT_ID,c.CREATOR_ID,TRUNC(C.CREATE_DATE)CREATE_DATE,\n" +
                "                                      C.COMPANY_ID, null CAR_LICENSE, -1 TIMES,\n" +
                "                                      (CASE\n" +
                "                                        WHEN P.CLASSIFY_ID LIKE 'S01%' THEN\n" +
                "                                         b.PRODUCT_QTY *\n" +
                "                                         TO_NUMBER(NVL(P.SPECIFICATIONS, 0))\n" +
                "                                        ELSE\n" +
                "                                         0\n" +
                "                                      END) SPRAY_PAINT_NUMBER\n" +
                "                                 FROM acc_voucher_ITEM B, \n" +
                "                                      acc_voucher C, MAS_PRODUCT P\n" +
                "                                WHERE B.VOUCHER_ID = C.ID\n" +
                "                                  AND GET_SERVE_TYPE_ID(B.PRODUCT_ID) = '007'\n" +
                "                                  and c.VOUCHER_TYPE = 'CUSTOMER_SALE'\n" +
                "                                  and c.SCENE_ID = 'SR004'\n" +
                "                                  AND P.ID = B.PRODUCT_ID\n" +
                "                                  AND C.CREATE_DATE >= :DATE_START\n" +
                "                                  AND C.CREATE_DATE < :DATE_END +1)\n" +
                "                       GROUP BY DEPT_ID, COMPANY_ID, TIMES, CAR_LICENSE,CREATOR_ID,CREATE_DATE)\n" +
                "              GROUP BY DEPT_ID, COMPANY_ID,CREATOR_ID,CREATE_DATE\n" +
                "\n" +
                "             UNION ALL\n" +
                "             SELECT NVL(SUM(AMOUNT), 0) AMOUNT, DEPT_ID, COMPANY_ID,\n" +
                "                    SUM(TIMES) TIMES,CREATOR_ID,CREATE_DATE,\n" +
                "                    SUM(SPRAY_PAINT_NUMBER) SPRAY_PAINT_NUMBER\n" +
                "               FROM (SELECT SUM(AMOUNT) AMOUNT, DEPT_ID, COMPANY_ID,\n" +
                "                             CAR_LICENSE, TIMES,CREATOR_ID,CREATE_DATE,\n" +
                "                             SUM(SPRAY_PAINT_NUMBER) SPRAY_PAINT_NUMBER\n" +
                "                        FROM (SELECT B.PRICE_TOTAL AMOUNT, C.DEPT_ID,\n" +
                "                                      C.COMPANY_ID, null CAR_LICENSE,C.CREATOR_ID,TRUNC(C.CREATE_DATE)CREATE_DATE,\n" +
                "                                      (CASE\n" +
                "                                        WHEN B.PRICE_TOTAL > 0 THEN\n" +
                "                                         1\n" +
                "                                        WHEN B.PRICE_TOTAL < 0 THEN\n" +
                "                                         -1\n" +
                "                                        ELSE\n" +
                "                                         0\n" +
                "                                      END) TIMES,\n" +
                "                                      (CASE\n" +
                "                                        WHEN P.CLASSIFY_ID LIKE 'S01%' THEN\n" +
                "                                         b.PRODUCT_QTY *\n" +
                "                                         TO_NUMBER(NVL(P.SPECIFICATIONS, 0))\n" +
                "                                        ELSE\n" +
                "                                         0\n" +
                "                                      END) SPRAY_PAINT_NUMBER\n" +
                "                                 FROM ACC_VOUCHER_ITEM B, \n" +
                "                                      ACC_VOUCHER C, MAS_PRODUCT P\n" +
                "                                WHERE B.VOUCHER_ID = C.ID\n" +
                "                                  AND C.VOUCHER_TYPE = 'CUSTOMER_SALE'\n" +
                "                                  AND P.ID = B.PRODUCT_ID\n" +
                "                                  and CLASSIFICATION in ('08','09')\n" +
                "                                  AND GET_SERVE_TYPE_ID(B.PRODUCT_ID) = '007'\n" +
                "                                  AND C.CREATE_DATE >= :DATE_START\n" +
                "                                  AND C.CREATE_DATE < :DATE_END +1)\n" +
                "                       GROUP BY DEPT_ID, COMPANY_ID, TIMES, CAR_LICENSE,CREATOR_ID,CREATE_DATE)\n" +
                "              GROUP BY DEPT_ID, COMPANY_ID,CREATOR_ID,CREATE_DATE) X LEFT JOIN HR_EMPLOYEE D ON x.PERSON_ID = D.ORIGIN" +
                " where 1 = 1 ");

        SalesReportUtils.appendCondition(sf,jo,"X.",true,"DEPT_ID");
        SalesReportUtils.appendCondition(sf,jo,"X.","PERSON_ID","POST_ID");
        return toJSONArray(find(sf.toString(), jo));

    }

    public JSONArray findTypeTimes(JSONObject jo){

        StringBuffer sf = new StringBuffer();
        sf.append("select sum(TIMES) TIMES\n" +
                " from RCL_SALES_TIMES_BUSINESS A " +
                " where a.DATE_OF_STATISTICS >= :DATE_START" +
                "  AND a.DATE_OF_STATISTICS <= :DATE_END");
        SalesReportUtils.appendCondition(sf,jo,"A.",true,"DEPT_ID");
        SalesReportUtils.appendCondition(sf,jo,"A.","POST_ID","PERSON_ID","SERVE_TYPE_ID");
        return ifEmptySetNewJO(toJSONArray(find(sf.toString(), jo)));
    }

    private JSONArray ifEmptySetNewJO(JSONArray jsonArray){
        if (jsonArray.isEmpty()) {
//            jsonArray.add(new JSONObject());
        }
        return jsonArray;
    }

    /**
     * 查询单据明细暂时弃用
     * @param jo
     * @return
     */
    private String getSalesAmountDetailSql(JSONObject jo){
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT TRUNC(B.APPROVE_DATE) CREATE_DATE,\n" +
                "       B.ID ID,\n" +
                "       E.CAR_LICENSE,\n" +
                "       '' PRODUCT_ID,\n" +
                "       '' PRODUCT_NAME,\n" +
                "       1 QTY,\n" +
                "       C.INCOME_AMOUNT PRICE_UNIT,\n" +
                "       0 DISCOUNT,\n" +
                "       C.INCOME_AMOUNT TOTAL_AMOUNT,\n" +
                "       C.INCOME_AMOUNT REAL_AMOUNT,\n" +
                "       b.INNER_BUSINESS_TYPE serve_type_id,\n" +
                "       '资金收入' serve_type_name,\n" +
                "       a.id dept_id," +
                "       'form/money.income.receipts' BUSINESS_ORDER_FORM," +
                "       'money.income.receipts.findOne' BUSINESS_ORDER_SEARCH,\n" +
                "       a.NAME dept_name\n" +
                "FROM MAS_DEPT A,\n" +
                "     MONEY_INCOME B,\n" +
                "     MONEY_INCOME_ITEM C,\n" +
                "     mas_amt_receipt D,\n" +
                "     FN_CUSTOMER_EXPAND E\n" +
                "WHERE A.COMPANY_ID = B.COMPANY_ID\n" +
                "  and c.AMT_RECEIPT_ID = d.ID\n" +
                "  AND E.ID = B.CUSTOMER_EXPAND_ID\n" +
                "  AND C.MONEY_INCOME = B.id\n" +
                "  AND A.id = B.DEPT_ID\n" +
                "  AND B.STATE = 'approve'\n" +
                "  AND B.APPROVE_DATE < :CREATE_DATE_E\n" +
                "  AND B.APPROVE_DATE >= :CREATE_DATE_S\n" +
                "UNION ALL\n" +
//                "--二手车佣金\n" +
                "SELECT TRUNC(DATE_APPROVE)   CRETATE_DATE,\n" +
                "       A.ID,\n" +
                "       A.LICENSE_PLATE ,\n" +
                "       '',\n" +
                "       '',\n" +
                "       1,\n" +
                "       A.STORE_PRICE,\n" +
                "       0,\n" +
                "       A.STORE_PRICE,\n" +
                "       A.STORE_PRICE ,\n" +
                "      A.INNER_BUSINESS_TYPE               ,\n" +
                "      '二手车佣金'               INNER_BUSINESS_TYPE_NAME,\n" +
                "       A.DEPT_ID,\n" +
                "       'form/used.usedCar' BUSINESS_ORDER_FORM," +
                "       'used.usedCar.findOne' BUSINESS_ORDER_SEARCH,\n" +
                "       B.NAME\n" +
                "FROM WAR_USED_CAR A\n" +
                "         LEFT JOIN MAS_DEPT B ON A.DEPT_ID = B.ID\n" +
                "WHERE a.DATE_APPROVE < :CREATE_DATE_E\n" +
                "  AND a.DATE_APPROVE >= :CREATE_DATE_S\n" +
                "  AND A.STATE = 'approve'\n" +
                "union all\n" +
//                "--车险佣金\n" +
                "SELECT TRUNC(A.CREATE_DATE) CREATE_DATE,\n" +
                "       a.ID,\n" +
                "       A.PLATE_LICENSE,\n" +
                "       '' PRODUCT_ID,\n" +
                "       '' PRODUCT_NAME,\n" +
                "       1,\n" +
                "       b.FN_COMMISSION,\n" +
                "       0,\n" +
                "       b.FN_COMMISSION,\n" +
                "       b.FN_COMMISSION,\n" +
                "       'PolicyInformation'  ,\n" +
                "       '有效保单'               INNER_BUSINESS_TYPE_NAME,\n" +
                "       DEPT_ID,\n" +
                "       'report/policyInformation.query' BUSINESS_ORDER_FORM," +
                "       'pub.query' BUSINESS_ORDER_SEARCH,\n" +
                "       (select name from MAS_DEPT s where s.id = a.DEPT_ID)\n" +
                "FROM POLICY_INFORMATION A,\n" +
                "     POLICY_INFORMATION_ITEM B\n" +
                "WHERE A.ID = B.POLICY_INFORMATION_ID\n" +
                "  AND A.CREATE_DATE < :CREATE_DATE_E\n" +
                "  AND A.CREATE_DATE >= :CREATE_DATE_S\n" +
//                "--接案介绍费\n" +
                "union all\n" +
                "SELECT\n" +
                "              trunc(A.CREATE_DATE)  CREATE_DATE,\n" +
                "       B.ID,\n" +
                "       C.CAR_LICENSE,\n" +
                "       '' PRODUCT_ID,\n" +
                "       '' PRODUCT_NAME,\n" +
                "       1 ,\n" +
                "        A.AMOUNT AMOUNT,\n" +
                "        0 AMOUNT,\n" +
                "               A.AMOUNT AMOUNT,\n" +
                "        A.AMOUNT AMOUNT,\n" +
                "\n" +
                "       'SettleExpense'       ,\n" +
                "       '接案介绍费'               ,\n" +
                "       A.DEPT_ID,\n" +
                "       'report/tc.query.dsxsetc' BUSINESS_ORDER_FORM," +
                "       'pub.query' BUSINESS_ORDER_SEARCH,\n" +
                "(select name from MAS_DEPT s where s.id = a.DEPT_ID)\n" +
                "FROM FN_SETTLE_EXPENSE A,\n" +
                "     RETAIL_ORDER B,\n" +
                "     FN_CUSTOMER_EXPAND C\n" +
                "WHERE A.EXPENSE_TYPE in\n" +
                "      ('infoExpense', 'innerInfoExpense')\n" +
                "  AND B.CUSTOMER_EXPAND_ID = C.ID\n" +
                "  AND A.TYPE = 'in'\n" +
                "  and B.ID = A.SOURCE\n" +
                "  and A.CREATE_DATE >= :CREATE_DATE_S\n" +
                "  AND A.CREATE_DATE < :CREATE_DATE_E\n" +
//                "union all\n" +
////                "--订金单\n" +
//                "SELECT TRUNC(A.DATE_APPROVE)                                    CREATE_DATE,\n" +
//                "       A.id,\n" +
//                "       C.CAR_LICENSE,\n" +
//                "       PRODUCT_ID PRODUCT_ID,\n" +
//                "       (select NAME from MAS_PRODUCT p where p.ID =PRODUCT_ID) PRODUCT_NAME,\n" +
//                "       b.PRODUCT_UOM_QTY QTY,\n" +
//                "       b.PRICE_TOTAL ,\n" +
//                "       0 ,\n" +
//                "       PRICE_TOTAL ,\n" +
//                "       PRICE_TOTAL ,\n" +
//                "       GET_SERVE_TYPE_ID(b.PRODUCT_ID) SERVE_TYPE,\n" +
//                "       GET_SERVE_TYPE_NAME(b.PRODUCT_ID)SERVE_TYPE_NAME,\n" +
//                "        DEPT_ID,\n" +
//                "       'form/advance.payment' BUSINESS_ORDER_FORM," +
//                "       'advance.payment.findOne' BUSINESS_ORDER_SEARCH,\n" +
//                "(select name from MAS_DEPT s where s.id = a.DEPT_ID)\n" +
//                "FROM ADVANCE_PAYMENT A,\n" +
//                "     ADVANCE_PAYMENT_ITEM B,\n" +
//                "     FN_CUSTOMER_EXPAND C\n" +
//                "WHERE A.STATE = 'approve'\n" +
//                "  AND A.CUSTOMER_EXPAND_ID = C.ID\n" +
//                "  AND A.ID = B.ADVANCE_PAYMENT_ID\n" +
//                "  AND A.DATE_APPROVE < :CREATE_DATE_E\n" +
//                "  AND A.DATE_APPROVE >= :CREATE_DATE_S\n" +
                "union all\n" +
//                "--零售单 (普通)\n" +
                "SELECT TRUNC(A.CREATE_DATE)                                  CREATE_DATE,\n" +
                "       A.id,\n" +
                "       c.CAR_LICENSE,\n" +
                "       PRODUCT_ID,\n" +
                "(select NAME from MAS_PRODUCT p where p.ID =PRODUCT_ID),\n" +
                "       PRODUCT_UOM_QTY,\n" +
                "       b.PRICE_UNIT,\n" +
                "       b.DISCOUNT_AMOUNT,\n" +
                "       b.PRICE_TOTAL,\n" +
                "       b.PRICE_TOTAL-b.DISCOUNT_AMOUNT,\n" +
                "       GET_SERVE_TYPE_ID(b.PRODUCT_ID)SERVE_TYPE,\n" +
                "       GET_SERVE_TYPE_NAME(b.PRODUCT_ID)SERVE_TYPE_NAME,\n" +
                "       a.DEPT_ID,\n" +
                "       'form/retail.order' BUSINESS_ORDER_FORM," +
                "       'retail.order.findOne' BUSINESS_ORDER_SEARCH,\n" +
                "(select name from MAS_DEPT s where s.id = a.DEPT_ID)\n" +
                "FROM RETAIL_ORDER A,\n" +
                "     RETAIL_ORDER_ITEM B,\n" +
                "     FN_CUSTOMER_EXPAND c\n" +
                "WHERE A.ID = B.SALE_ID\n" +
                "  and a.CUSTOMER_EXPAND_ID = c.id\n" +
                "  and ORDER_TYPE not in ('agencyPay', 'noDirectPay', 'directPay', 'DS0')" +
                "  AND A.CREATE_DATE < :CREATE_DATE_E\n" +
                "  AND A.CREATE_DATE >= :CREATE_DATE_S\n" +
                "UNION ALL\n" +
//                "--分销单\n" +
                "SELECT TRUNC(A.CREATE_DATE)                                                 CREATE_DATE,\n" +
                "       A.id,\n" +
                "       c.CAR_LICENSE,\n" +
                "       PRODUCT_ID,\n" +
                "       (select NAME from MAS_PRODUCT p where p.ID =PRODUCT_ID),\n" +
                "       b.PRODUCT_UOM_QTY,\n" +
                "       PRICE_UNIT,\n" +
                "       B.DISCOUNT,\n" +
                "       b.PRICE_TOTAL,\n" +
                "       b.PRICE_TOTAL-B.DISCOUNT,\n" +
                "        GET_SERVE_TYPE_ID(b.PRODUCT_ID)SERVE_TYPE,\n" +
                "       GET_SERVE_TYPE_NAME(b.PRODUCT_ID)SERVE_TYPE_NAME,\n" +
                "       DEPT_ID,\n" +
                "       'form/sale.order' BUSINESS_ORDER_FORM," +
                "       'sale.order.findOne' BUSINESS_ORDER_SEARCH,\n" +
                "       (select name from MAS_DEPT s where s.id = a.DEPT_ID)\n" +
                "FROM SALE_ORDER A,\n" +
                "     SALE_ORDER_ITEM B,\n" +
                "     FN_CUSTOMER_EXPAND C\n" +
                "WHERE A.ORDER_TYPE IN ('BZ_SALE', 'FN_SALE')\n" +
                "  AND A.ID = B.SALE_ID\n" +
                "  and a.CUSTOMER_EXPAND_ID = c.ID\n" +
                "  AND A.DATE_APPROVE < :CREATE_DATE_E\n" +
                "  AND A.DATE_APPROVE >= :CREATE_DATE_S\n" +
                "UNION ALL\n" +
//                "--分销退回\n" +
                "SELECT TRUNC(A.CREATE_DATE)                                  CREATE_DATE,\n" +
                "       A.id,\n" +
                "       c.CAR_LICENSE,\n" +
                "       PRODUCT_ID,\n" +
                "       (select NAME from MAS_PRODUCT p where p.ID =PRODUCT_ID),\n" +
                "       b.PRODUCT_UOM_QTY,\n" +
                "       PRICE_UNIT,\n" +
                "       B.DISCOUNT,\n" +
                "       b.PRICE_TOTAL,\n" +
                "       b.PRICE_TOTAL-B.DISCOUNT,\n" +
                "        GET_SERVE_TYPE_ID(b.PRODUCT_ID)SERVE_TYPE,\n" +
                "       GET_SERVE_TYPE_NAME(b.PRODUCT_ID)SERVE_TYPE_NAME,\n" +
                "       DEPT_ID,\n" +
                "       (CASE ORDER_TYPE WHEN 'SaleReturn.orderType.A' THEN 'form/sale.saleReturnWithOrder' WHEN 'SaleReturn.orderType.B' THEN 'form/sale.saleReturnNoOrder' ELSE 'form/sale.saleReturnWithOrder' END ) BUSINESS_ORDER_FORM," +
                "       (CASE ORDER_TYPE WHEN 'SaleReturn.orderType.A' THEN 'sale.saleReturnWithOrder.findOne' WHEN 'SaleReturn.orderType.B' THEN 'sale.saleReturnNoOrder.findOne' ELSE 'sale.saleReturnNoOrder.findOne' END ) BUSINESS_ORDER_SEARCH," +
                "       (select name from MAS_DEPT s where s.id = a.DEPT_ID)\n" +
                "FROM SALE_RETURN A,\n" +
                "     SALE_RETURN_ITEM B,\n" +
                "     FN_CUSTOMER_EXPAND c\n" +
                "WHERE A.STATE = 'done'\n" +
                "  AND A.CUSTOMER_EXPAND_ID = C.ID\n" +
                "  AND A.ID = B.SALE_RETURN_ID\n" +
                "  AND A.DATE_DONE < :CREATE_DATE_E\n" +
                "  AND A.DATE_DONE >= :CREATE_DATE_S\n" +
                "UNION ALL\n" +
//                "--凭证平台内客户\n" +
                "SELECT TRUNC(A.CREATE_DATE)                                 CREATE_DATE,\n" +
                "       A.id,\n" +
                "       c.CAR_LICENSE,\n" +
                "       PRODUCT_ID,\n" +
                "       (select NAME from MAS_PRODUCT p where p.ID =PRODUCT_ID),\n" +
                "       b.PRODUCT_QTY,\n" +
                "       PRICE_UNIT,\n" +
                "       B.DISCOUNT,\n" +
                "       b.PRICE_TOTAL,\n" +
                "       b.PRICE_TOTAL-B.DISCOUNT,\n" +
                "        GET_SERVE_TYPE_ID(b.PRODUCT_ID)SERVE_TYPE,\n" +
                "       GET_SERVE_TYPE_NAME(b.PRODUCT_ID)SERVE_TYPE_NAME,\n" +
                "       DEPT_ID,\n" +
                "       'form/purch.voucher' BUSINESS_ORDER_FORM," +
                "       'voucher.findOne' BUSINESS_ORDER_SEARCH,\n" +
                "       (select name from MAS_DEPT s where s.id = a.DEPT_ID)\n" +
                "FROM ACC_VOUCHER A,\n" +
                "     ACC_VOUCHER_ITEM B,\n" +
                "     FN_CUSTOMER_EXPAND c\n" +
                "WHERE A.ID = B.VOUCHER_ID\n" +
                "  AND A.CUSTOMER_EXPAND_ID = C.ID\n" +
                "  AND A.VOUCHER_TYPE = 'CUSTOMER_SALE'\n" +
                "  AND A.INVENTORY_MODE = 'IM_OWN'\n" +
//                "  AND nvl(A.SCENE_ID, ' ') <> 'SAL001' -- 标准销售分销单 单独SQL 取数\n" +
                "  AND ((SELECT T.PARTY_ID FROM MAS_PARTY_ROLE T WHERE T.ID = A.COMPANY_ID) !=\n" +
                "       (SELECT T.PARTY_ID FROM MAS_PARTY_ROLE T WHERE T.ID = A.PARTNER_ID))\n" +
                "  AND NVL(A.CLASSIFICATION, ' ') <> 'CROSS_IC'\n" +
                "  AND (A.ORIGIN_BUSINESS_TYPE NOT IN ('RetailReturn', 'ProfitAndLoss') OR\n" +
                "       (A.ORIGIN_BUSINESS_TYPE = 'RetailReturn' AND\n" +
                "        A.CLASSIFICATION IN ('08', '09')))\n" +
                "  AND A.CREATE_DATE < :CREATE_DATE_E\n" +
                "  AND A.CREATE_DATE >= :CREATE_DATE_S\n"
//                "union all\n" +
//                //"--会员联盟\n" +
//                "select trunc(STATISTIC_DATE)          CREATE_DATE,\n" +
//                "       '',\n" +
//                "       '',\n" +
//                "       '',\n" +
//                "       '',\n" +
//                "       1,\n" +
//                "       settlement_amount AMOUNT,\n" +
//                "       0,\n" +
//                "       settlement_amount,\n" +
//                "       settlement_amount,\n" +
//                "       'vip_union_report',\n" +
//                "       '会员联盟',\n" +
//                "       DEPT_ID,\n" +
//                "       'form/money.income.receipts' BUSINESS_ORDER_FORM," +
//                "       'money.income.receipts.findOne' BUSINESS_ORDER_SEARCH,\n" +
//                "       (select d.NAME from MAS_DEPT d where d.ID = a.DEPT_ID)\n" +
//                "from vip_union_report A\n" +
//                "where DEPT_ID is not null\n" +
//                "      AND A.STATISTIC_DATE < :CREATE_DATE_E\n" +
//                "  AND A.STATISTIC_DATE >= :CREATE_DATE_S"
        );
        return salesAmountDetailSqlPack(sf.toString(),jo);
    }

    private String salesAmountDetailSqlPack(String sql,JSONObject jo){
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT X.CREATE_DATE,\n" +
                "       X.ID,\n" +
                "       to_char(X.CREATE_DATE,'YYYY-MM-DD')CREATE_DATE_CHAR,\n" +
                "       to_char(Max(X.CREATE_DATE) over (  ),'yyyy-mm-dd')date_of_statistics_e,\n" +
                "       to_char(min(X.CREATE_DATE) over (  ),'yyyy-mm-dd')date_of_statistics_s,\n" +
                "       X.CAR_LICENSE,\n" +
                "       X.PRODUCT_ID,\n" +
                "       X.PRODUCT_NAME,\n" +
                "       X.QTY,\n" +
                "       X.PRICE_UNIT,\n" +
                "       X.DISCOUNT,\n" +
                "       X.TOTAL_AMOUNT-DISCOUNT TOTAL_AMOUNT,\n" +
                "       X.REAL_AMOUNT,\n" +
                "       X.SERVE_TYPE_ID,\n" +
                "       X.SERVE_TYPE_NAME,\n" +
                "       X.DEPT_ID,\n" +
                "       X.DEPT_NAME,\n" +
                "       X.BUSINESS_ORDER_FORM,\n" +
                "       X.BUSINESS_ORDER_SEARCH\n" +
                "FROM ("+ sql +") X" +
                " where 1=1 ");
        SalesReportUtils.appendCondition(sf,jo,"X.","DEPT_ID","SERVE_TYPE_ID","PRODUCT_ID");
        return sf.toString();
    }

    public JSONArray findSalesAmountDetail(JSONObject jo){

//        String detailSql = getSalesAmountDetailSql(jo);
        StringBuffer sf = getSalesAmountPayMethodDetailSql(jo);
        StringBuffer group = new StringBuffer();
        group.append("select M.DATE_OF_STATISTICS,\n" +
                "       date_of_statistics_e,\n" +
                "       date_of_statistics_s,\n" +
                "       CREATE_DATE_CHAR,\n" +
                "       COMPANY_ID,\n" +
                "       COMPANY_NAME,\n" +
                "       DEPT_ID,\n" +
                "       DEPT_NAME,\n" +
                "       SUM(SALE_MONEY)SALE_MONEY,\n" +
                "       SUM(REAL_AMOUNT)REAL_AMOUNT,\n" +
                "       SOURCE,\n" +
                "       SOURCE_NAME,\n" +
                "       BUSINESS_ORDER_FORM,\n" +
                "       BUSINESS_ORDER_SEARCH,\n" +
                "       PAYMENT_METHOD_TYPE_ID,\n" +
                "       PAYMENT_METHOD_TYPE_NAME\n" +
                "from (" + sf.toString() +
                ") M\n" +
                "GROUP BY date_of_statistics_e, M.DATE_OF_STATISTICS, date_of_statistics_s, CREATE_DATE_CHAR, COMPANY_ID, COMPANY_NAME,\n" +
                "         DEPT_ID, DEPT_NAME, SOURCE, SOURCE_NAME, BUSINESS_ORDER_FORM, BUSINESS_ORDER_SEARCH, PAYMENT_METHOD_TYPE_ID,\n" +
                "         PAYMENT_METHOD_TYPE_NAME" +
                " order by date_of_statistics_e asc");


        return ifEmptySetNewJO(toJSONArray(find(group.toString(), jo)));
    }

    public JSONArray findGdpDetail(JSONObject jo){

        String sf = getSalesAmountDetailSql(jo);
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(sf+" and X.serve_type_id not in ('010', '012')");


        return ifEmptySetNewJO(toJSONArray(find(stringBuffer.toString(), jo)));
    }

    private StringBuffer findGdpDetailSql(JSONObject jo) {
        StringBuffer sf = new StringBuffer();
        sf.append("select A.DATE_OF_STATISTICS,\n" +
                "       to_char(A.DATE_OF_STATISTICS,'yyyy-mm-dd')  CREATE_DATE_CHAR,\n" +
                "       A.COMPANY_ID,\n" +
                "       A.COMPANY_NAME,\n" +
                "       A.DEPT_ID,\n" +
                "       A.DEPT_NAME,\n" +
                "       A.CUSTOMER_ID,\n" +
                "       A.CUSTOMER_NAME,\n" +
                "       A.SALE_MONEY,\n" +
                "       A.SALE_MONEY REAL_AMOUNT,\n" +
                "       A.SOURCE,\n" +
                "       A.SOURCE_NAME,\n" +
                "       A.ORIGIN,\n" +
                "       A.ORIGIN_NAME,\n" +
                "       A.PAYMENT_METHOD_TYPE_ID,\n" +
                "       A.PAYMENT_METHOD_TYPE_NAME\n" +
                "from RCL_DAILYSALESREPORT_PAYMENT A\n" +
                " where PAYMENT_METHOD_TYPE_ID NOT in ('10', '05', '12')\n" +
                "        and nvl(ORIGIN, ' ') not in\n" +
                "            ('LossQuotation.orderType.A', 'LossQuotation.orderType.B', 'LossQuotation.orderType.C', 'lossPaymentUpdate',\n" +
                "             'SaleReturn.orderType.A', 'SaleReturn.orderType.B',\n" +
                "             'innerInfoExpense')"+
                "      AND A.DATE_OF_STATISTICS < :CREATE_DATE_E\n" +
                "  AND A.DATE_OF_STATISTICS >= :CREATE_DATE_S");
        SalesReportUtils.appendCondition(sf,jo,"A.","DEPT_ID");
        sf.append(" union all ");
        sf.append("select trunc(STATISTIC_DATE)                   STATISTIC_DATE,\n" +
                "       to_char(A.STATISTIC_DATE, 'yyyy-mm-dd') CREATE_DATE_CHAR,\n" +
                "       A.COMPANY_ID,\n" +
                "       GET_COMPANY(COMPANY_ID),\n" +
                "       DEPT_ID,\n" +
                "       (select name from MAS_DEPT s where s.id = DEPT_ID),\n" +
                "       PARTNER_ID,\n" +
                "       '',\n" +
                "       NVL(settlement_amount, 0)               SALE_MONEY,\n" +
                "       NVL(settlement_amount, 0)               AMOUNT,\n" +
                "       'vipUnion',\n" +
                "       '跨公司结算',\n" +
                "       'vipUnion',\n" +
                "       '跨公司结算',\n" +
                "       A.TYPE,\n" +
                "       (CASE A.TYPE WHEN 'in' then '收入' else '支出' end)\n" +
                "from vip_union_report A\n" +
                "where DEPT_ID is not null\n" +
                "  and a.STATISTIC_DATE > :CREATE_DATE_S\n" +
                "  and a.STATISTIC_DATE < :CREATE_DATE_E");
        SalesReportUtils.appendCondition(sf,jo,"A.","DEPT_ID");
        return getSalesAmountPayMethodDetailSqlPacket(sf);
    }

    private StringBuffer getSalesAmountPayMethodDetailSql(JSONObject jo) {
        StringBuffer sf = new StringBuffer();
        sf.append("select A.DATE_OF_STATISTICS,\n" +
                "       to_char(A.DATE_OF_STATISTICS,'yyyy-mm-dd')  CREATE_DATE_CHAR,\n" +
                "       A.COMPANY_ID,\n" +
                "       A.COMPANY_NAME,\n" +
                "       A.DEPT_ID,\n" +
                "       A.DEPT_NAME,\n" +
                "       A.CUSTOMER_ID,\n" +
                "       A.CUSTOMER_NAME,\n" +
                "       A.SALE_MONEY,\n" +
                "       A.SALE_MONEY REAL_AMOUNT,\n" +
                "       A.SOURCE,\n" +
                "       A.SOURCE_NAME,\n" +
                "       A.ORIGIN,\n" +
                "       A.ORIGIN_NAME,\n" +
                "       A.PAYMENT_METHOD_TYPE_ID,\n" +
                "       A.PAYMENT_METHOD_TYPE_NAME\n" +
                "from RCL_DAILYSALESREPORT_PAYMENT A\n" +
                " where A.DATE_OF_STATISTICS < :CREATE_DATE_E +1 \n" +
                "   AND A.DATE_OF_STATISTICS >= :CREATE_DATE_S" +

//                "   and ORIGIN_NAME not in ('直赔','定损报价')" +
                "   AND PAYMENT_METHOD_TYPE_ID NOT in ('10',  '12','05')\n" +
                "");
        SalesReportUtils.appendCondition(sf,jo,"A.","DEPT_ID");
        sf.append(" union all ");
        sf.append("select trunc(STATISTIC_DATE)                   STATISTIC_DATE,\n" +
                "       to_char(A.STATISTIC_DATE, 'yyyy-mm-dd') CREATE_DATE_CHAR,\n" +
                "       A.COMPANY_ID,\n" +
                "       GET_COMPANY_NAME(COMPANY_ID),\n" +
                "       DEPT_ID,\n" +
                "       (select name from MAS_DEPT s where s.id = DEPT_ID),\n" +
                "       PARTNER_ID,\n" +
                "       '',\n" +
                "       NVL(settlement_amount, 0)               SALE_MONEY,\n" +
                "       NVL(settlement_amount, 0)               AMOUNT,\n" +
                "       'vipUnion',\n" +
                "       '跨公司结算',\n" +
                "       'vipUnion',\n" +
                "       '跨公司结算',\n" +
                "       A.TYPE,\n" +
                "       (CASE A.TYPE WHEN 'in' then '收入' else '支出' end)\n" +
                "from vip_union_report A\n" +
                "where DEPT_ID is not null\n" +
                "  and a.STATISTIC_DATE >= :CREATE_DATE_S\n" +
                "  and a.STATISTIC_DATE < :CREATE_DATE_E + 1");
        SalesReportUtils.appendCondition(sf,jo,"A.","DEPT_ID");
        return getSalesAmountPayMethodDetailSqlPacket(sf);
    }

    private StringBuffer getSalesAmountPayMethodDetailSqlPacket(StringBuffer sf){
        String sqlCore = sf.toString();
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select X.date_of_statistics,\n" +
                "       Max(create_date_char) over (  )date_of_statistics_e,\n" +
                "       min(create_date_char) over (  )date_of_statistics_s,\n" +
                "       create_date_char,\n" +
                "       company_id,\n" +
                "       company_name,\n" +
                "       dept_id,\n" +
                "       dept_name,\n" +
                "       customer_id,\n" +
                "       customer_name,\n" +
                "       sale_money,\n" +
                "       real_amount,\n" +
                "       source,\n" +
                "       source_name,\n" +
                "       (case\n" +
                "                  when SOURCE = 'RetailOrder' and ORIGIN not in ('LossQuotation.orderType.A','LossQuotation.orderType.B','LossQuotation.orderType.C') then 'report/retail.retailOrderPatMethod.query'\n" +
                "                  when SOURCE = 'RetailOrder' and ORIGIN in ('LossQuotation.orderType.A','LossQuotation.orderType.B','LossQuotation.orderType.C') then 'report/retail.retailOrderPatMethodLoss.query'\n" +
                "                  when SOURCE = 'AdvancePayment' then 'report/advance.payment.query'\n" +
                "                  when SOURCE = 'PolicyInformation' then 'report/policyInformation.query'\n" +
                "                  when SOURCE = 'vipUnion' then 'report/vip.surplus.vipSurplusSK'\n" +
                "                  when SOURCE = 'SaleOrder' then 'report/sale.order.query'\n" +
                "                  when SOURCE = 'SaleReturn' then 'report/sale.saleReturn.query'\n" +
                "                  when SOURCE = 'MoneyIncome' then 'report/moneyIncome.query'\n" +
                "                  when SOURCE = 'UsedCar' then 'report/used.usedCar.query'\n" +
                "                  when SOURCE = 'SettleExpense' then 'report/fn.Quotation.expense'\n" +
                "                  else '' end)              BUSINESS_ORDER_FORM,\n" +
                "             (case\n" +
                "                  when SOURCE = 'RetailOrder' then 'pub.query'\n" +
                "                  else  'pub.query' end)     BUSINESS_ORDER_SEARCH,\n" +
                "       origin,\n" +
                "       origin_name,\n" +
                "       payment_method_type_id,\n" +
                "       payment_method_type_name\n" +
                "from (" +sqlCore +"" +
                ") X");
        return stringBuffer;
    }

    public JSONArray findCostDetail(JSONObject jo){
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT A.DATE_OF_STATISTICS," +
                "       to_char(Max(A.DATE_OF_STATISTICS) over (  ),'yyyy-mm-dd')date_of_statistics_e,\n" +
                "       to_char(min(A.DATE_OF_STATISTICS) over (  ),'yyyy-mm-dd')date_of_statistics_s,\n" +
                "       to_char(A.DATE_OF_STATISTICS,'yyyy-mm-dd') CREATE_DATE_CHAR,\n" +
                "       A.COMPANY_ID,\n" +
                "       A.COMPANY_NAME,\n" +
                "       A.DEPT_ID,\n" +
                "       A.DEPT_NAME,\n" +
                "       A.COST,\n" +
                "       A.SUPPLY_CHAIN_MANAGEMENT_FEE,\n" +
                "       A.PLATFORM_MANAGEMENT_FEE,\n" +
                "       A.SALE_MONEY,\n" +
                "       A.INVENTORY_MODE,\n" +
                "       A.SOURCE,\n" +
                "       A.SOURCE_NAME,\n" +
                "       A.ORIGIN,\n" +
                "       A.ORIGIN_NAME,\n" +
                "       A.PRODUCT_ID,\n" +
                "       A.PRODUCT_NAME,\n" +
                "       A.DEPT_AREA,\n" +
                "       A.BUSINESS_CIRCLE_ID,\n" +
                "       A.BUSINESS_CIRCLE_NAME,\n" +
                "       A.TYPE_DEPT,\n" +
                "       A.PRODUCT_COST," +
                "       (case\n" +
                "           when SOURCE = 'SaleOrder' then 'report/sale.order.query'\n" +
                "           when SOURCE = 'LossSaleReturn' then 'report/lossSale.returnSale.query'\n" +
                "           when SOURCE = 'LossSaleAdjust' then 'report/lossSaleAdjustReturnSale.query'\n" +
                "           when SOURCE = 'ServicePlan' then 'report/fn.servicePlanItem.query'\n" +
                "           when SOURCE = 'InventoryTransferAsset' then 'report/inventory.transfer.asset.query'\n" +
                "           when SOURCE = 'CrossCompanyTransferNew' then 'report/cross.company.transferNew.query'\n" +
                "           when SOURCE = 'PurchaseOrder' then 'report/purchase.order.query'\n" +
                "           when SOURCE = 'PurchaseAfterJunior' then 'report/purchase.after.junior'\n" +
                "           when SOURCE = 'LossQuotation' then 'report/lossQuotation.query'\n" +
                "           when SOURCE = 'Pick' then 'report/lossSale.returnSale.query'\n" +
                "               when SOURCE = 'ProfitAndLoss' then 'report/stock.productLoss.query'\n" +
                "           when SOURCE = 'SaleReturn' then 'report/sale.saleReturn.query'\n" +
                "           when SOURCE = 'RetailOrder' and ORIGIN not in ('directPay','directPay') then 'report/retail.order.query'\n" +
                "           when SOURCE = 'RetailOrder' and ORIGIN in ('directPay','directPay') then 'report/retail.orderLoss.query'\n" +
                "           when SOURCE = 'RetailReturn' and ORIGIN = 'RetailReturn.orderType.A' then 'report/retail.returnWithOrder.query'\n" +
                "           when SOURCE = 'RetailReturn' and ORIGIN = 'RetailReturn.orderType.B' then 'report/retail.returnNoOrder.query'\n" +
                "           when SOURCE = 'ConsumableUse' then 'report/consumableuse.order.query'\n" +
                "           when SOURCE = 'InternalHelpReturnApproval' then 'report/internalHelp.return.query'\n" +
                "           when SOURCE = 'Voucher' then 'report/voucher.query'\n" +
                "           else '' end) BUSINESS_ORDER_FORM," +
                "       'pub.query'  BUSINESS_ORDER_SEARCH\n" +
                " FROM RCL_COST_REPORT A " +
                "  where 1 = 1"+
                "  and ORIGIN not in ('yearcard','storedValue')\n" +
                "   and a.DATE_OF_STATISTICS >= :CREATE_DATE_S\n" +
                "   and a.DATE_OF_STATISTICS < :CREATE_DATE_E + 1" );
//        SalesReportUtils.appendCondition(sf,jo,"A.","DEPT_ID");
//        sf.append(
//                "  union all " +
//                " select trunc(STATISTIC_DATE)," +
//                " to_char(Max(A.STATISTIC_DATE) over ( ), 'yyyy-mm-dd') date_of_statistics_e,\n" +
//                "       to_char(min(A.STATISTIC_DATE) over ( ), 'yyyy-mm-dd') date_of_statistics_s,\n" +
//                "       to_char(A.STATISTIC_DATE, 'yyyy-mm-dd')\n" +
//                "                                                                 CREATE_DATE_CHAR,\n" +
//                "       A.COMPANY_ID,\n" +
//                "       GET_COMPANY_NAME(A.COMPANY_ID)COMPANY_NAME,\n" +
//                "       A.DEPT_ID,\n" +
//                "       (select name from MAS_DEPT s where s.ID = a.DEPT_ID)DEPT_NAME,\n" +
//                "       a.PLATFORM_MANAGEMENT_FEE COST,\n" +
//                "       0 SUPPLY_CHAIN_MANAGEMENT_FEE,\n" +
//                "       A.PLATFORM_MANAGEMENT_FEE,\n" +
//                "       A.SETTLEMENT_AMOUNT SALE_MONEY,\n" +
//                "       '' INVENTORY_MODE,\n" +
//                "       'vipUnionReport' SOURCE,\n" +
//                "       '会员联盟' SOURCE_NAME,\n" +
//                "       ' ' ORIGIN,\n" +
//                "       ' ' ORIGIN_NAME,\n" +
//                "       ' ' PRODUCT_ID,\n" +
//                "       '会员联盟' PRODUCT_NAME,\n" +
//                "       ' ' DEPT_AREA,\n" +
//                "       ' ' BUSINESS_CIRCLE_ID,\n" +
//                "       ' ' BUSINESS_CIRCLE_NAME,\n" +
//                "       ' ' TYPE_DEPT,\n" +
//                "       0 PRODUCT_COST,\n" +
//                "       'report/vip.surplus.vipSurplusRBB' BUSINESS_ORDER_FORM,\n" +
//                "       'pub.query' BUSINESS_ORDER_SEARCH\n" +
//                " FROM VIP_UNION_REPORT A\n" +
//                "WHERE  STATISTIC_DATE >= :CREATE_DATE_S" +
//                "  and STATISTIC_DATE < :CREATE_DATE_E"
//
//        );
        SalesReportUtils.appendCondition(sf,jo,"A.","DEPT_ID");

        sf.append(" order by DATE_OF_STATISTICS");

        return ifEmptySetNewJO(toJSONArray(find(sf.toString(), jo)));
    }


    public JSONArray findSalesReportDay(JSONObject jo){
        StringBuffer sf = new StringBuffer();
        sf.append("select SUM(SALE_MONEY),SUM(PROFIT)PROFIT,nvl(SUM(TIMES),0)TIMES," +
                "(CASE sum(SALE_MONEY) WHEN 0 THEN 0 ELSE ROUND((sum(PROFIT) / sum(SALE_MONEY)) * 100, 2) END) PROFIT_R," +
                "SERVE_TYPE_ID\n" +
                "from RCL_CATEGORY_REPORT_DAY\n" +
                "where DEPT_ID IN :DEPTID \n" +
                "AND DATE_OF_STATISTICS >= to_date(:STARTDATEs,'YYYY-mm-dd')\n" +
                "AND DATE_OF_STATISTICS < to_date(:ENDDATEs,'YYYY-mm-dd') +1" +
                " group by SERVE_TYPE_ID ");

        return ifEmptySetNewJO(toJSONArray(find(sf.toString(), jo)));
    }

    public JSONArray findSalesReportMonth(JSONObject jo){
        StringBuffer sf = new StringBuffer();
        sf.append("select SUM(SALE_MONEY),SUM(PROFIT)PROFIT,nvl(SUM(TIMES),0)TIMES," +
                "(CASE sum(SALE_MONEY) WHEN 0 THEN 0 ELSE ROUND((sum(PROFIT) / sum(SALE_MONEY)) * 100, 2) END) PROFIT_R," +
                "SERVE_TYPE_ID\n" +
                "from RCL_CATEGORY_REPORT_DAY\n" +
                "where DEPT_ID IN :DEPTID \n" +
                "AND to_char(DATE_OF_STATISTICS,'YYYYmm') = :YEAR_AND_MONTH\n" +
                " group by SERVE_TYPE_ID ");

        return ifEmptySetNewJO(toJSONArray(find(sf.toString(), jo)));
    }


}
